August 26, 2009 at 12:01 pm
Essentially we have a full backup from last night, and then a diff every 4 hours and trn backup every hour.
It looks like they are not part of the same media set, so I am not sure if that makes a difference.
Essentially, I want to restores the full backup, 2 diffs, and 4 trn.
How do I do this, i tried using the Gui, but it says something like expecting 7 media sets?
Need help asap 🙁
August 26, 2009 at 1:17 pm
Restore the full, be sure that you use the 2nd tab with "norecovery" option set. Pick the file manually for the restore in the dialog.
Same for the diff.
Then the logs.
When you are done, you can recover the database with a RESTORE DATABASE xxx WITH RECOVERY.
If you made backups with multiple files selected, you need to select those files when restoring. That can be an issue.
August 26, 2009 at 1:51 pm
Expanding a bit on Steve's post.
Restore the full WITH NORECOVERY
Restore the latest differential WITH NOERCOVERY. You don't need both differentials
Restore all the log backups, in sequence, starting with the one that ran after the last differential, all WITH NORECOVERY
When you're sure that you're restored all the logs you need, run
RESTORE DATABASE <Database name> WITH RECOVERY
I would suggest you use scripts, not the gui, so that you can be 100% sure that you're restoring exactly what you want to restore. Furthermore, if you have time, do a trial run on another server first, make sure that you can restore everything in order, correctly, and get what you want out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2009 at 2:05 pm
If you want to recover to a particular point in time (12:00 pm), you will need to add the STOPAT time to your transaction log restores to make sure you do not go past then.
RESTORE LOG MyDatabase
FROM DISK = 'X:\DBBackup\MyDatabase_backup_200908261603.trn'
WITH NORECOVERY, STOPAT = '2009-08-26 12:00:00.000'
August 27, 2009 at 2:39 pm
Do you want to restore on top of the existing database ? Or restore to a new database & leave the current intact ? You can use this option to practice, restoring your "Prod_DB" to Prod_DB_Prcatice" to make sure you get what you want.
Using the GUI is easier if you're not familiar with the code, but the Code gives you a better idea of what's being run. You can use the GUI to set up your restore, but instead of pressing "OK", choose the "Script to New Window" which will generate the T-SQL script so you can learn the code, save it for future use and modify it if needed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply