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?

4
  • Is the database read-only? – Dan Guzman Feb 23 at 13:03
  • 2 hours restore seems to be too long. Is IFI enabled on the instance? – Denis Rubashkin Feb 23 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 – GarthMarenghee Feb 23 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. – Stephen Morris - Mo64 Feb 23 at 14:20
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.

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? – GarthMarenghee Feb 23 at 14:47
  • You beat me to it with the technical explanation. – John K. N. Feb 23 at 14:54
  • I'm getting a 'This backup cannot be restored using WITH STANDBY because a database upgrade is needed' error? – GarthMarenghee Feb 23 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. – David Browne - Microsoft Feb 23 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.

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 – GarthMarenghee Feb 23 at 14:46
  • I'm getting a 'This backup cannot be restored using WITH STANDBY because a database upgrade is needed' error? – GarthMarenghee Feb 23 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. – John K. N. Feb 23 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.