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:

Always restore the database as new database(never restor over the existing db). 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.

How to use 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'