Restoring a SQL database is easy in DPM. A click here and click there. For me though the difficult time was figuring out how to do a “Point In Time” restore. This is my success story. YMMV.
First off, for a database to be recover to a specific time after a backup has taken place, the database must be running in FULL recovery mode. If it is set to SIMPLE you cannot recover to a point in time. This option is set through the SQL Server Manager.
Okay, let’s go.
First step is to locate the database you wish to recover in DPM under the Recovery section. Because you want to go back in time you cannot choose the latest backup. You want to choose a time before the actual “Point in Time” you want to recover to. Check out the first picture. I had been taking backups every 15 minutes. The point in time I want to recover to is at 1:25PM. So, I choose 1:16PM.
Oh yeah! A wizard. First screen of the wizard shows just the basic info.
Second screen allows you to recover the database to the original location, a different SQL server that is also being protected by DPM, a network folder, or to copy the database to tape. I will be choosing to recover to a different instance of SQL. This gives me the ability to choose the original server, but also allows me to change the name of the database so not to overwrite.
On the next screen you choose which SQL server to recover your database to. Along with the option to rename the database and choose where you want the database to be located at on the disk.
The next screen is the most important for the point in time recovery. You will want to choose the second option, “Leave database non-operational”. You will also want to check the check box in that section and specify where to copy the transaction logs to.
The next screen I click through as I don’t do SAN snapshots.
Once the restore completes open the Microsoft SQL Server Management Studio. You will see the database you restored on the left side after logging into the SQL server you restored it to under “databases”. The database will be listed as “Restoring…”.
Right click on the database and choose, Tasks, Restore, Transaction Log.
The Restore Transaction Log screen will appear. Under Restore Source choose the radio button “From file or tape” and then located the transaction log that was recovered by DPM. Hint: When selecting the log you will need to change the file type you are looking for from “Backup Files(*.bak,*.trn) to All Files(*) as the recovered log file has an extension of log.
Make sure that the Restore check box next to the transaction log is NOT checked.
At the bottom of the screen under “Restore to”, choose point in time and then set the time at which you want the restore to stop reading the log. I’m not sure why I am telling you this part as it doesn’t work for me. If I click OK after setting this the restore process will run through the entire log and update the database to the last record. This is NOT what I want.
To resolve this problem I click Script at the top of the “Restore Transaction Log” screen. Then click the “Cancel” button.
After clicking cancel you will be presented with a SQL query for the restore. We are going to make a slight change to this query to set the point in time we want to restore to. At the end of the first line add the following with your changes for the time you want to restore till:
, stopat = ‘Oct 16, 2013 1:25:00 PM’;
It should look something like this.
Click the “! Execute” button on the toolbar.
The messages window at the bottom should show “100 percent processed.”, along with some other stats.
Yay! You’re done.