Restoring from one full database backup and a whole lot of log backups 1

[TheBugSlayer]

Hi all.

I have a situation where I have to restore a database to a different location. There are nightly backups, seven days a week, and log backups taken every thirty (30) minutes. On 10/3 the nightly backup failed and on 10/10 the last transaction log backup was taken. We did not realize this until the 25th. I figured we had run out of disk space and when I cleared some, nightly backups started running the 2on 10/29. So On that day the first full backup since 10/2 was successful and the first transaction log backup since 10/10 was successful.

Now, I was asked to restore the database in its state prior to 10/29 to a different location. So I restored the 10/2 backup WITH NORECOVERY and also restored the first transaction log backup on 10/2, WITH NORECOVERY. My concern is, there are 375 log transaction files between 10/2 and 10/10. Do I need to restore every single one of those files? Almost certain the answer is yes. Now, is there a way to perform this task in a batch or some other fast way? At this point I am considering writing a script that will loop through each file restoring it WITH NORECOVERY and the last one WITH RECOVERY.

What I want from you is to validate my method, and to suggest any other way to go restore the DB to the state it was in the 10th.

Thank you!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[SQLBill]

Yes, you would have to restore all the log backups one-by-one. You could write a script to update the restore command with the next log backup name and just loop through the names. I've never tried that though.

Let me pose a question for the future...why are you doing a full backup each night? Have you considered doing a full backup once a week, a differential once a night, and t-logs through the day? With this method, you could have restored the last good full backup, the differentials, and then just the log backups from the last day needed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

Hi Bill. Thank you for taking the time to answer.

As a matter of fact, I modified a script that I found only to suit my needs. I had restored the last full from the 2nd the script was going through the log files when the person who had requested it said he only needed the last full backup restored, no logs! I made sure to tell him to be more specific next time...

Bill, I like the schedule you are describing. See, they brought some MS certified DBA, he is the one who setup the backups this way. He doesn't use the normal maintenance plans but Ola Hallengren's scripts, which is nice but it does not do file clean-up; that part I added. So I had no say in the decision. However, I don't like the number of log files that you have to go through to restore the database for more than one day from the last full backup. I will try to change this.

For our local database we do a weekly full plus the other maintenance tasks, which lasts in total around 8h. Then daily we do a...full backup! Makes no sense, does it? I thought it was a differential. We also backup the log daily. I believe here the daily should be changed to differential...don't you think? They last on average 1h.

Thanks for your advice.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[SQLBill]

These comments are based on normal database maintenance....your business requirements may not allow this.

You could change the daily full backups to differentials. It would save time. You would still need to make sure nothing happened to the full backup on the weekend. Then you would only have to restore the full backup, differential closest to the day you need restored and then any log backups from that day. Easier than restoring hundreds of log backups.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

Roger that. We'll see what I can do here. As far as business requirements, backup logs might still be taken every 30 minutes but I will not have to keep the files more than a day if prior day's differential was successful. Backups had failed due to lack of space in the first place. Thanks again.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).