So I am performing a daily full database backup (midnight) and I am backing up the transaction logs hourly. All backups are done in separate files per database with timestamps.

Now say if I want to restore my database to 3PM on a particular day, I use management studio and restore the full database from that day (WITH NORECOVERY), and then one by one I restore the transaction logs. Now this process is tedious since I need to open browse to several files one at a time. Is there a better way? Am I doing the right thing backing up to several files in the first place?



That is the correct process to go through. You can do it in SSMS or you can create a SQL Script that does all of this using the RESTORE DATABASE and RESTORE LOG statements.

Imagine doing that when you backup the log every 10 minutes. You'll go from 15 to 90 files (plus the full).

You can generate the RESTORE LOG scripts using the data in the msdb database. Look at the tables which start with backup.

  • +1 - If it's an emergency and you don't have any predefined scripts, just set up the restore job in the SMSS and hit the "script" button, then copy/paste that as many times as you need to, changing the file name each time. – Mark Henderson Feb 10 '11 at 1:12

Can you select multiple files when you're browsing for files to restore? Just Shift-Click or CTRL-click the files to select more than one.

  • This won't work, as it expects all of the log files to be from the same backup set, which they aren't. – Mark Henderson Feb 10 '11 at 1:12

As above, here is some TSQL

   FROM DISK = 'f:\Documents.bak'
   FROM DISK = 'f:\Documents35.trn'

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.