RESTORE DATABASE WITH STOPAT

Hi All,

Today I came across a question on MSDN:

We are using SQL Server 2005. We have a database say "TESTSTOPAT", this database is in Full Recovery Model. Unfortunately we don't have any backup for this database. Today some one deleted records from one of the table.
Now we want to recover from those changes. What we did is took a Full backup of database and then tried to restore it with a new name with STOPAT option but it fails with below errors:

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 DATABASE is terminating abnormally.

To be frank till the time I also didn't look to this option i.e. STOPAT clause with Full Database, as previously this option was coming for LOG files only (in SQL 2000). I did some research and based on that below are my findings.


**************************************************
RESTORE DATABASE WITH STOPAT
**************************************************

Previously in SQL 2000 STOPAT clause was provided for RESTORE LOG wherein you can "halt" the recovery proces at 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. RESTORE DATABASE WITH STOPAT wow great thing lets test it.

STEP :1
************

--Created a new database.

CREATE DATABASE TESTSTOPAT
GO


--Created a table in this newly created database and inserted values. Time when this query was executed was 14-Oct-2010 20:1043.827.

USE TESTSTOPAT
GO
CREATE TABLE TESTSTOPAT (I INT, II INT)
GO
INSERT INTO TESTSTOPAT VALUES (1,1)
INSERT INTO TESTSTOPAT VALUES (2,2)
INSERT INTO TESTSTOPAT VALUES (3,3)
INSERT INTO TESTSTOPAT VALUES (4,4)
INSERT INTO TESTSTOPAT VALUES (5,5)
INSERT INTO TESTSTOPAT VALUES (6,6)
INSERT INTO TESTSTOPAT VALUES (7,7)
INSERT INTO TESTSTOPAT VALUES (8,8)
INSERT INTO TESTSTOPAT VALUES (9,9)
INSERT INTO TESTSTOPAT VALUES (10,10)
INSERT INTO TESTSTOPAT VALUES (11,11)
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:10:43.827


STEP :2
***********

--Then we execute a DELETE statement. Time when this query was executed was 14-Oct-2010 20:11:40.437.

DELETE FROM TESTSTOPAT WHERE II>9
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:11:40.437


STEP :3
***********

--Then we took a full database backup. Time when this backup occurs was 14-Oct-2010 20:13:14.640.

BACKUP DATABASE TESTSTOPAT TO DISK='C:\TESTSTOPATFULL.BAK'
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:13:14.640

STEP :4
***********

--Now lets try to restore this database, I want to restore it before execution of STEP 2 i.e. Delete statement as I want to recover from that DELETE operation.

RESTORE DATABASE [TESTSTOPAT2]
FROM DISK = N'C:\TESTSTOPATFULL.BAK'
WITH FILE = 1, MOVE N'TESTSTOPAT' TO N'C:\TESTSTOPAT2.mdf', MOVE N'TESTSTOPAT_LOG' TO N'C:\TESTSTOPAT2_LOG.LDF', NOUNLOAD, REPLACE, STATS = 10,STOPAT='OCT 14, 2010 20:11:00 PM'
GO

Output

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 DATABASE is terminating abnormally.


Looking at the output what we saw is that RESTORE is failing. But why, MS has provided option of RESTORING database with STOPAT then why this is failing.

Here comes the reason
*****************************

As per Books on Line

A full backup (formerly known as a database backup) backs up the entire database, including part of the transaction log (so that the full backup can be recovered). Full backups represent the database at the time the backup completed. The transaction log included in the full backup allows it to be used to recover the database to the point in time at which the backup was completed.

That means Full Database backup can only be restored to the time at which backup was completed not prior to that. So if this the case then


• 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 (like it does above).

• 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.

Happy Learning....................