Full Recovery Mode

Point-In-Time recoveries require the database to be in full recovery as we are going to be using the log backup to finalize the restore.  In this scenario, I take a full backup nightly and then transactional log backups regularly throughout the day.  We will need the full backup file and ALL the transaction logs taken to the point in time to which we want to recover.  In my case it is just one; however, there could be many–it just depends on your backup strategy and the time of the failure/error.

This post won’t go over the backups themselves, and I just realized I don’t have a post on those.  I will have to link once I set it up.

Restoring A Database

If the database doesn’t exist, already you would right-click on Databases, then choose Restore Database …

 

If we are restoring a copy of the database on the same instance, we can choose Database as the source and then choose the files we wish to restore from the backup history.  In this example, I choose the source of device and choose both the backup file and the transaction log backup, which you can see highlighted in the box.  Clicking on the Timeline button on the restore to line allows us to specify the time to which we want to restore.

I only need to choose Specific date and time and I can see the options available to me.  I can either modify the time field or I can use what looks like a progress bar to slide to the time I want.  As I have chosen the database name on the previous window, all the logic needed to restore my database has been updated and the database will restore to the time I put in the window.

If the database already exists on the server, the steps are similar; however, we will have to break up the restore into two steps.   First, we restore the database and set the recovery to NoRecovery.  We only have the option to choose Tasks->Restore->Transaction Logs when the database is in recovery mode.

There’s Always T-SQL

And we also have the option of restoring the database via T-SQL and while there are lots of options, these seem to work well for me.  This is also done in two steps.  First the Full Restore–and I KNOW you don’t have your databases on the C: drive, so please forgive my poor example.  I’ve got kids to feed and can’t afford big servers with lots of disks.

[sourcecode language=”sql”]Restore Database RestoreTest
FROM DISK = ‘C:TEMPAW.bak’
WITH MOVE ‘AdventureWorks2012_Data’ TO ‘C:MSSQLDATARestoreTest_Data.mdf’
, MOVE ‘AdventureWorks2012_Log’ TO ‘C:MSSQLDATARestoreTest_log.ldf’
, NORECOVERY, STATS = 10[/sourcecode]

Then the transaction log.  The secret sauce is the STOPAT option.  That’s it.  I hope you won’t have to use it everyday; however, when that error or issue comes your way, with this process you will able to get back to the point you wanted.

[sourcecode language=”sql”]RESTORE LOG RestoreTest
FROM DISK = ‘C:TempAW.trn’
WITH RECOVERY,
STOPAT = N’2014-07-28T21:48:47′[/sourcecode]