Home / server / Questions / 233632
In Process
Luis
Asked: 2011-02-10 15:59:42 +0800 CST

Sql Server Database Restore with Management Studio

  • 772

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

sql-server database-restore

3 Answers

  1. 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.

    • 2

  2. 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.

    • 0

  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;
    • 0