T-SQL Tuesday #85: STOP! Restore Time!

I know, I know. That was a bit much.

This month’s T-SQL Tuesday is being hosted by Kenneth Fisher (b | t). T-SQL Tuesday (#TSQL2sDAY) is a blog party founded by Adam Machanic (b | t). Each month a member of the community hosts the party and selects a topic for us to write about.

Ken invites us to write about Backup and Recovery. I felt that I covered a lot of ground in my performance tuning backup and restore operations post, so I decided to contribute to Tim Ford’s (b | t) entry level content challenge. In this post, I will cover the STOPAT, STOPATMARK, and STOPBEFOREMARK options of the RESTORE command.

What’s the Point?

All three of the options, STOPAT, STOPATMARK, and STOPBEFOREMARK, are used for point-in-time recovery. Point-in-time recovery is where you can select a given date/time or transaction event to restore your database to. Here are a few scenarios.

  • STOPAT: At 2:13 am, your application began reporting fatal errors when running a select statement. You ran DBCC CHECKDB and it indicated corruption in your clustered index. In this case, you may want to restore your database to 2:12 am in search of the most recent time when your database was not corrupted.
  • STOPBEFOREMARK: You are about to deploy a major change to your database. You insert a record into a metadata table in your database before you begin, to mark the version change. The deployment fails and you need to restore back to the time before the deployment. It would be ideal to restore up to but not including the transaction which inserted the metadata record.
  • STOPATMARK: Similar to the last scenario, you are doing a deployment. This deployment is in one large transaction. After the deployment, the QA team runs automated tests, forgetting that this is the production environment. You need to remove the test data from your database. You then would like to restore the database but do not want to redo the deployment so you include the deployment transaction and then stop, not including the test data which was inserted after.

Each of the options that we are going to explain use the transaction logs. For them to function, we need the database to be in FULL or BULK_LOGGED recovery models. Transaction log backups are not possible in the SIMPLE recovery model.

STOPAT

In order to restore point-in-time, you need to restore the full backup with NORECOVERY. This tells SQL Server not to initiate crash recovery which is a process that performs the redo and undo operations on your database to roll back the uncommitted transactions and roll forward the committed ones.

Once the full backup is restored you will need to restore the rest of the LSN (log sequence number) chain in the appropriate order. If you are not using differential backups, this means that you need to restore each log file until you cover the point-in-time that you are targeting. If you do have one or more differential backups, just restore the most recent differential which was taken before your target point-in-time and then all log backups between then and the target.

Important Note: You want to be careful not to over-shoot your target. If that happens you will need to start the restores over again which can waste time. For that, I recommend that you put your STOPAT option on every restore command. This will guarantee that you do not move too far. The point-in-time does not have to exist in the backup to work. Logs which are too new will restore nothing and logs which are older and do not contain the point-in-time will restore all of their data. The log which contains the point-in-time will restore up to and including the time.

Demonstration

This example is going to be very simple. I created a table which can store our time stamp.

CREATE TABLE dbo.PointInTime
(dt DATETIME2(7) NOT NULL)

I then inserted a few rows, minutes apart.

I am now going to restore my backup files up until and including the ‘2016-12-12 23:53:45.2430000’ time stamp.

RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime.bak'
WITH NORECOVERY, REPLACE, STOPAT = '2016-12-12 23:53:45.243'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_1.trn'
WITH NORECOVERY, STOPAT = '2016-12-12 23:53:45.243'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_2.trn'
WITH NORECOVERY, STOPAT = '2016-12-12 23:53:45.243'

Once the files are restored, you need to restore the database with RECOVERY. This initiates the crash recovery process I mentioned earlier and your database will come online.

RESTORE DATABASE AdventureWorks2014
WITH RECOVERY

SELECT * FROM dbo.PointInTime

As you can see, I have my records from before the target time stamp and it included row 4 which matches our target time, but none of the newer records exist.

STOPATMARK and STOPBEFOREMARK

Recovering to a marked transaction requires some forethought. You have to have created a marked transaction before you can restore to it. In order to mark a transaction, you must execute and commit a transaction which is logged. This means that you need to perform some sort of DDL (data definition language) or DML (data manipulation language) command in the transaction. Read only operations, such as SELECT statements will not be sufficient.

Marked Transaction Demonstration

Let us dive right in by adding a column to our PointInTime table and then inserting some data with marked transactions.

BEGIN TRANSACTION AddedColumnToPointInTime WITH MARK 'Not the mark, just the description'

ALTER TABLE dbo.PointInTime ADD Mark VARCHAR(30) NULL

COMMIT
GO

BEGIN TRANSACTION InsertedIntoPointInTime WITH MARK 'Not the mark, just the description'

INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')

COMMIT
GO

A transaction can be named without being marked. AddColumnToPointInTime and InsertedIntoPointInTime are named transactions. You must use the WITH MARK option to get a mark into the transaction log with the optional description.

SELECT * FROM dbo.PointInTime

You can confirm the marks and/or look them up in the future by querying MSDB.

SELECT * FROM msdb.dbo.logmarkhistory

STOPATMARK Demonstration

The STOPATMARK option will restore your database to the point when the marked transaction was committed. This includes the marked transaction in the recovery.

RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime.bak'
WITH NORECOVERY, REPLACE

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_1.trn'
WITH NORECOVERY, STOPATMARK = 'AddedColumnToPointInTime'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_2.trn'
WITH NORECOVERY, STOPATMARK = 'AddedColumnToPointInTime'

RESTORE DATABASE AdventureWorks2014
WITH RECOVERY

By restoring to the AddedColumnToPointInTime mark, we have the column but none of the data which was inserted.

STOPBEFOREMARK Demonstration

In the STOPATMARK scenario we kept our table change but not the data. Imagine that these two transactions were part of a single deployment and you need to restore to right before the deployment began. This is when you should use STOPBEFOREMARK which will restore to the marked transaction but roll back that transaction during crash recovery.

RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime.bak'
WITH NORECOVERY, REPLACE

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_1.trn'
WITH NORECOVERY, STOPBEFOREMARK = 'AddedColumnToPointInTime'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_2.trn'
WITH NORECOVERY, STOPBEFOREMARK = 'AddedColumnToPointInTime'

RESTORE DATABASE AdventureWorks2014
WITH RECOVERY

Now our table is back to the way it was before we deployed the changes.

STOPATMARK [AFTER ‘datetime’]

There was a time when I would write dynamic SQL to make sure that my marked transaction names were unique, thus allowing me to restore to the point I intended while having an automated process which would insert them periodically.

I recently learned that this is not necessary because you can specify the AFTER option and tell it to stop at a mark after a specific date/time. This can be useful in a lot of cases. You may do weekly deployments and want to reuse the same marked transaction name each time. Also, you may have an ETL job that runs every 4 hours. It can use a marked transaction and then you can specify the recovery time if something were to go wrong.

Demonstration

BEGIN TRANSACTION InsertedIntoPointInTime WITH MARK

INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')
INSERT INTO dbo.PointInTime (dt, MARK)
VALUES (GETDATE(),'InsertedIntoPointInTime')

COMMIT TRANSACTION InsertedIntoPointInTime

waitfor delay '00:00:06'
GO 5 --Execute 5 times

I have added a few more marked transactions and spaced them out in time. Note that we have 32 rows in our table.

RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime.bak'
WITH NORECOVERY, REPLACE

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_1.trn'
WITH NORECOVERY, STOPATMARK = 'InsertedIntoPointInTime' AFTER '2016-12-13 20:32:04.053'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_2.trn'
WITH NORECOVERY, STOPATMARK = 'InsertedIntoPointInTime' AFTER '2016-12-13 20:32:04.053'

RESTORE LOG AdventureWorks2014
FROM DISK = 'C:\Backups\AdventureWorks2014_PointInTime_3.trn'
WITH NORECOVERY, STOPATMARK = 'InsertedIntoPointInTime' AFTER '2016-12-13 20:32:04.053'

RESTORE DATABASE AdventureWorks2014
WITH RECOVERY

The row count dropping to 20 confirms that we have successfully restored up to and including the third mark with the name InsertedIntoPointInTime. This was the first marked transaction with that name after 8:32:04.053.

Wrap-up

In this post, you learned how to use the various point-in-time recovery options, allowing us to stop at marked transactions and time stamps. Including marked transactions or logging time stamps as part of your normal business processes will open recovery options and make you look like a hero when a restore is needed.

Leave a Reply

%d bloggers like this: