SQL Server point in time restore

By: Greg Robidoux

Overview

The RESTORE ... WITH STOPAT option allows you to restore your database to a point in time.  This gives you the ability to restore a database prior to an event that occurred that was detrimental to your database.  In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.

Explanation

When data is written to your database it is first written to the transaction log and then to the data file after the transaction is complete.  When you restore your transaction log, SQL Server will replay all transactions that are in the transaction log and roll forward or roll back transactions that it needs to prior to putting the database in a useable state.

Each of these transactions has a LSN (logical sequence number) along with a timestamp, so when restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.

One thing to note is that if your database is using the Bulk-Logged recovery model and there is a minimally logged operation (such as a bulk insert) in the transaction log you can not do a point in time recovery using that transaction log.  But if you have another transaction log backup that occurred later and this does not have a minimally logged operation you can still use this transaction log to do a point in time recovery, but the point in time you are referencing has to occur within this second transaction log backup.

T-SQL

Restore database with STOPAT
This will restore the AdventureWorks database to at point in time equal to "March 23, 2009 at 5:31PM".

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH NORECOVERY
GO 
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' 
WITH RECOVERY, 
STOPAT = 'Mar 23, 2009 05:31:00 PM' 
GO

Restore database with STOPAT where recovery model is Bulk-Logged and there is a minimally logged operation
In this example we have a full backup and the transaction log has a minimally logged operation.  We can try to do a point in time recovery using the commands below:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH NORECOVERY
GO 
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' 
WITH RECOVERY, 
STOPAT = 'Mar 23, 2009 05:31:00 PM' 
GO

But if there are bulk operations we will get this error.

The restore operation will complete, but it will restore the entire transaction log backup and leave the database in a "restoring" state.  You could then either restore additional transaction logs or use the RESTORE .. WITH RECOVERY option to bring the database back online.

SQL Server Management Studio

To restore to a point in time using SSMS do the following, select the backup and the transaction logs you want to restore and then use the "To a point in time." option as shown below to select the point in time you want to recover the database to.

Restoring to a point in time with a bulk-logged operation in the transaction log
If you try to restore using SSMS you will get the following error message, similar to what we got with the T-SQL code.