The role of STOPAT in RESTORE DATABASE

Previously in SQL Server 2000, the STOPAT clause was provided for RESTORE LOG wherein you can “halt” the recovery process at a particular point in time. This feature is very useful for recovering from accidental user errors and such.


Now in SQL Server 2005, there is a STOPAT clause provided in the RESTORE DATABASE command also. The first time I saw this option, I was confused and wondered how we can provide a point-in-time recovery with a full database backup – because the full database backup is essentially an image of the database and does not contain the entire log backup (only contains the portion which changed during the backup command itself.) But I never thought about it much after that.


So when a customer asked me how this feature works, I dug in a little bit and here’s what I found:



  • Without log backups, there is simply no way to achieve a true point-in-time restore

  • What we do when we specify STOPAT in RESTORE DATABASE is to test if the full database backup is already ahead of the point in time you want to stop at. If it is, then the command errors out.

  • Why it errors out is to provide you a clear signal that even if you restore this full backup and then restore subsequent transaction log backups on top (specifying a STOPAT) then those transaction log backups would fail with errors since the database is already rolled forward to a point in time ahead of the STOPAT

  • In the above case, you would have wasted a lot of time and effort first restoring the database and then the transaction log backup before finding out that you should have started with a earlier full backup.

So, STOPAT in RESTORE DATABASE can be a little disappointing if you are not aware of the real purpose of this clause. Please review http://msdn2.microsoft.com/en-us/library/ms191468(SQL.90).aspx which explains this in detail.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s