SQL Server Performance Forum – Threads Archive

How to restore last available Transactional Backup

Hi All, I am testing my backup strategy by restoring the backups on test server. Our backup policy is:
Full backup : Every night at 2 AM
Differential backup : Every 4 hours
Transactional Backup : Every 10 minutes. The command for transactional backup in scheduled job is as below: BACKUP LOG [Booking] TO DISK = N’g:Booking_Backup’ WITH NOINIT , NOUNLOAD , NAME = N’Transactional backup’, NOSKIP , STATS = 10, DESCRIPTION = N’TRANSACTIONAL LOG BACKUP’, NOFORMAT the same command is run with INIT option just after Differential backup. In the restore process, after full and Differential restore, when i try to restore last available transaction log backup an error appear stating that "The log in the backup set begins at LSN …., which is too late to apply. An earlier backup available to restore.." Does this mean that I have to restore all the transactional backup one by one sequentially? If there are 20 transactional backup after last differential backup then would I have to run more then 20 restore command to bring the database online? Is there any option that allows to restore the last transactional backup directly? Please advice. Regards,
Subhash

If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name WITH NORECOVERY). Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY. Recover the database (RESTORE DATABASE database_name WITH RECOVERY). Alternatively, this step can be combined with restoring the last log backup.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

OK. Thanks Satya. So there are two ways to restore the transactional backup. First – One by one in sequence with NORECOVERY option. Second – Directly to any backup with RECOVERY option. Now I am clear with restore process. Thanks a lot. Regards,
Subhash

Only for the last set of trnasaction log you have to use WITH RECOVERY in order to make that database available. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Once you restore database WITH RECOVERY you can’t apply any more tlog backups if any exists or if you want to… You can follow as Satya mentioned or
You can all full and tlog backup with NORECOVERY… once it is verified there are no log backup to be restored then you simply run DUMMY restore WITH RECOVERY… EX:RESTORE DATABASE DBNAME WITH RECOVERY
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>