1

Initial note I am not a DBA, I am learning on the job.

Where I work we have an antiquated third-party system that sends us a .bak file once a week and log files every hour. To get the previous day's data in the database we have a process that runs a python script which builds the SQL to to do the full restore. The restore takes about 2 hours every night. We have a request from our end users to get the data as "live" as possible, which is theoretically possible as we receive the log file every hour. However I haven't found a way to restore log files only, as it appears a full restore has to be done before you can restore transaction log files.

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this? Say by only restoring the latest log file or by running a partial restore so we don't have to restore all the files every time?

Improve this question
4
  • Is the database read-only? Commented Feb 23, 2021 at 13:03
  • 2 hours restore seems to be too long. Is IFI enabled on the instance? Commented Feb 23, 2021 at 13:30
  • 1
    The database is not read only, it's 2 hours the bak file is absolutely massive with 13 years of data in it (and growing). The restore fails if a log file is missing (as they have to be loaded sequentially) so theoretically we could significantly speed up the process if we could force the database into NORECOVERY mode and restore the latest log/s files Commented Feb 23, 2021 at 13:50
  • To elaborate on what Dan Guzman wrote, if when you have restored the database you ONLY need to read the data i.e. for reports then you could restore with norecovery and standby which allows read-only access to the database and (once you have disconnected the readonly users) allows you to apply further transaction logs to roll the database further forward in time without the need to restore another full backup. Commented Feb 23, 2021 at 14:20

2 Answers 2

Reset to default
3

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this?

Assuming you run the same version of SQL Server, Yes. What you're looking for is called RESTORE WITH STANDBY:

RESTORE WITH STANDBY Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement.

Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.

Every time you apply another log backup users will need to be disconnected briefly. This is how Log Shipping works, and so long as they send you all the transaction log backups, you don't need to restore the weekly full. Just apply the next log backup in the chain.

Improve this answer
4
  • Great thanks. In regards to the stand by file, how do I generate that or can I just set that to the bak file that's used in the full restore? Commented Feb 23, 2021 at 14:47
  • You beat me to it with the technical explanation. Commented Feb 23, 2021 at 14:54
  • I'm getting a 'This backup cannot be restored using WITH STANDBY because a database upgrade is needed' error? Commented Feb 23, 2021 at 15:11
  • Yes. If you are restoring on a newer SQL Server version a database upgrade is required before the database can be brought online, even in standby. You can look in the SQL Server ERRORLOG file to see the version upgrades applied to determine what version you would need to run. Commented Feb 23, 2021 at 15:13
1

As has already been posted by David Browne - Microsoft, I would like to expand on the concept I was using in my answer form the comemnt.

If you have a database, you can use the WITH STANDBY... feature and have the database restore to a certain point-in-time (your last TLOG backup) and then have the database in Standby / Read-Only mode.

The steps you wold have to perform are:

USE [master] RESTORE DATABASE [AdminDB2] FROM DISK = N'C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20210220_223019.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5, REPLACE RESTORE DATABASE [AdminDB2] FROM DISK = N'C:\SQL\Backup\AdminDB2\DIFF\AdminDB2_DIFF_20210222_223007.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [AdminDB2] FROM DISK = N'C:\SQL\Backup\AdminDB2\LOG\AdminDB2_LOG_20210222_231512.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [AdminDB2] FROM DISK = N'C:\SQL\Backup\AdminDB2\LOG\AdminDB2_LOG_20210223_001513.trn' WITH FILE = 1, STANDBY = N'C:\SQL\BACKUP\AdminDB2_RollbackUndo_2021-02-23_15-22-54.bak', NOUNLOAD, STATS = 5

With the last transaction log restore you are telling the database to stay in standby mode (STANDBY = N'C:\SQL\BACKUP\AdminDB2_RollbackUndo_2021-02-23_15-22-54.bak') which allows you to apply additional TLOG backups to the database.

An additional restore of a TLOG backup is as simple as running the following command for the next TLOG Restore:

RESTORE LOG [AdminDB2] FROM DISK = N'C:\SQL\Backup\AdminDB2\LOG\AdminDB2_LOG_20210223_011512.trn' WITH FILE = 1, STANDBY = N'C:\SQL\BACKUP\AdminDB2_RollbackUndo_2021-02-23_15-22-54.bak', NOUNLOAD, STATS = 5

As you can see from the time-stamps on the TLOG backup files I am constantly applying the newest hourly TLOG backup to the Standby / Read-only database AdminDB2.

... AdminDB2_LOG_20210223_001513.trn AdminDB2_LOG_20210223_011512.trn ...

Reference: RESTORE Statements (Transact-SQL) (Microsoft | SQL Docs)

After restoring the database the elements of the database stay accessibly, just in Read-only mode.

Screen shot of database AdminDB2 datbase restored in Standby and Read_only Mode

Disclaimer: The actual restore and screen shot were taken today. No production data was harmed during the process.

Answering Your Question

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this? Say by only restoring the latest log file or by running a partial restore so we don't have to restore all the files every time?

As long as you keep on receiving TLOG backups on an hourly basis, you should be able to keep on restoring the latest TLOG backup to the STANDBY database, ad infinitum (without end or limit).

You would only have to apply a FULL restore after your TLOG chain breaks.

Possible Issues

If the restore to STANDBY fails, because of the error message you are receiving:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed'

..then you can retrieve the version of the database from the backup file with:

RESTORE HEADERONLY FROM DISK = N'C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20210220_223019.bak'

This will produce an output with a column SoftwareVersionMajor, SoftwareVersionMinor and the SoftwareVersionBuild.

(using my example backup file)

SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild 12 0 6433

You can then lookup the information on Microsoft's page:

Determine the version, edition, and update level of SQL Server and its components

...which in my case returns:

SQL Server 2014
CU4 + GDR for SP3 (12.0.6433.1 - January 2021)

Version Major : 12 Version Minor : 0 Version Build : 6433

So I would have to install SQL Server 2014 with Service Pack 3 and the CU3 with GDR to be able to restore the backup file. Your mileage my vary.

Improve this answer
3
  • 1
    This is fantastic! I think it's going to take me a little time to apply it properly but this looks like the perfect solution Commented Feb 23, 2021 at 14:46
  • I'm getting a 'This backup cannot be restored using WITH STANDBY because a database upgrade is needed' error? Commented Feb 23, 2021 at 15:11
  • The SQL Server versions of your client's database and your test instance aren't the same SQL Server version then. This requires the database to be updated at the end of the restore process. Verify you are using the same SQL Server versions and Service Pack Levels. Commented Feb 23, 2021 at 15:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.