Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server point in time restore



By:

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.

Msg 4341, Level 16, State 1, Line 1
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Msg 4338, Level 16, State 1, Line 1
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

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.


Last Update: 3/17/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools