INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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).

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

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: What should I know before I post?

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

(OP)
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).

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

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: What should I know before I post?

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

(OP)
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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close