0

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

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
1
  • +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. Commented Feb 10, 2011 at 1:12
0

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.

Improve this answer
1
  • This won't work, as it expects all of the log files to be from the same backup set, which they aren't. Commented Feb 10, 2011 at 1:12
0

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;
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.