Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

Thanks

share|improve this question

3 Answers 3

As above, here is some TSQL

RESTORE DATABASE Documents
   FROM DISK = 'f:\Documents.bak'
   WITH NORECOVERY;
RESTORE LOG Documents
   FROM DISK = 'f:\Documents35.trn'
   WITH NORECOVERY;
share|improve this answer

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.

share|improve this answer
    
+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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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