Point in Time Recovery
August 31, 2005
Every once in a while, an event might happen that corrupts a database. We have all made a stupid mistake at least once that has trashed a database. When this happens what do you do? If you do not have a database backup, then you had better own up to the problem you caused and tell your boss that you screwed up. If you do have at least a complete database backup then you most likely will be able to recover the corrupted database, up to the point that you corrupted the data. This article will discuss how to use a point in time restore to recover your databases.
What is a point in time recovery?
So what is a point in time recovery? A point in time recovery is restoring a database to a specified date and time. When you have completed a point in time recovery, your database will be in the state it was at the specific date and time you identified when restoring your database. A point in time recovery is a method to recover your database to any point in time since the last database backup.
What does it take to do a point in time recovery?
In order to perform a point in time recovery you will need to have an entire series of backups (complete, differential, and transaction log backups) up to and/or beyond the point in time in which you want to recover. If you are missing any backups, or have truncated the transaction log without first performing a transaction log backup, then you will not be able to perform a point in time recovery. At a minimum, you will need a complete backup and all the transaction log backups taken following the complete backup. Optionally if you are taking differential backups, then you will need the complete backup, the last differential backup prior to the corruption, then all the transaction log backups taken following the differential backup.
What to consider once you know your database is corrupted
As soon as you know your database is corrupted, you need to consider a couple of things. The first thing to review is when were the last complete, differential, and/or transaction log backup taken. If there has not been a transaction log backup taken since the database was corrupted, then you should take one immediately. This is because the transactions prior to, plus the transaction that corrupted your database are contained in the current transaction log. Getting the current transaction log records into a transaction log backup will allow you to perform a point in time recovery up to when your database was corrupted.
A method to identify when the database got corrupted
Hopefully, you know the specific time frame when the database was corrupted. If you do not know exactly when the database was corrupted, but you would like to recover your database to the specific time just before it was corrupted, then what can you do? One method is to perform a series of point in time recoveries, then check the database for corruption after each recovery. You would keep doing recoveries until you narrowed down the time frame for when the corruption occurred. Keep in mind this method could be very time consuming. I would only consider doing this if you really need to recover up to the second prior to the corruption and you have unlimited time to perform multiple point in time restores to determine the exact time when the database was corrupted.
How to Perform a Point in Time Recovery Using Enterprise Manager
A point in time restore can be performed using Enterprise Manager. To bring up the restore screen, expand the databases, right click on the database you want to restore, select the "All Task" option, and then click on the "Restore Database " option. Doing this will bring up the following screen:
On this screen, you will find a "Point in time restore" check box. To perform a point in time restore just click on this box to check it. You will only be able to check this box if there is a transaction log backup listed amongst the list of backups. After checking this box, the following screen will be displayed:
On this screen, you specify the date and time that you want the restore process to stop restoring. This will be the point in time for which your database will be restored. Note that the default date and time displayed when this screen is first displayed is the point in time for the last transaction contained in the transaction log backup selected on the prior screen. Therefore, if you want to stop the restore prior to the end of the transaction log you will need to specify a date and time that is less then the original date/time displayed. Below is what I would enter if I wanted to restore my database to 3:20 PM on 8/21.
As you can see it is very easy to perform a point in time restore using Enterprise Manager.
Using T-SQL to perform a point in time restore
If you do not like using Enterprise Manager to perform restores or want to script the restore operations so your restore can be scheduled, you can use the T-SQL "RESTORE" command. To do a point in time restore you will need to issue two different "RESTORE" commands. The first "RESTORE" command will be to restore the database from the complete backup, and the second command will be to perform the point in time restore of the transaction log backup. Below is an example of two different "RESTORE" commands that I would used to issue the same point in time restore as the Enterprise Manager example above:
RESTORE DATABASE [test] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test_Complete' WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY
RESTORE LOG [test] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test_Transaction' WITH FILE = 3, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N'8/21/2005 3:20:00 PM'
When your database is corrupted, and you do not want to lose all the transactions processed since your last complete backup then a point in time restore is the solution you need. A point in time restore will allow you to stop your restore operation just prior to the point in time that your database was corrupted. Using the point in time restore operation can save your customers unnecessary rework to re-enter transactions that might be lost if you where to perform only a complete backup. Next time you have a corrupted database, you might consider if using a point in time restore will eliminate the extra effort of re-keying in transactions lost if you where to not perform a point in time restore.