Usage of STOPATMARK to restore database

One of the unknown feature of SQL Server 2008 is STOPATMARK to restore databases. The idea of use STOPATMARK option is to restore an entire set of databases to a mutually consistent state at some earlier point in time.

The mark hast to be set using a named transaction with the WITH mark option. The syntax is:

BEGIN TRAN <name> WITH MARK <description>

With the STOPAT option, the transaction log restore only recovers if the mark is present in the backup file. If the transaction log mark is not present in the log backup, the log backup is restored but the database is not recovered, so that another log backup could be restored. This means the RECOVERY option can be included in every restore command in the sequence but only be applied once the mark is found. 

The STOPATMARK Option:

• Can only be performed using T-SQL
• Transaction marked using:

BEGIN TRAN <name> WITH MARK <description>

• Restore has 2 related options:
• STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward
• STOPBEFOREMARK rolls forward to the mark and excludes marked the transaction from the roll forward
• If the mark is not present in the transaction log backup, the backup is restored, but the database is not recovered.

Example:

CREATE DATABASE STOPATMARKDB;

GO

USE STOPATMARKDB;

GO

ALTER DATABASE STOPATMARKDB SET Recovery FULL

GO

CREATE TABLE TestTable

(

ID                   INT IDENTITY(1,1),

DataValue     VARCHAR(50) NOT NULL

);

GO

INSERT INTO TestTable(DataValue) VALUES ('The First Entry');

BACKUP DATABASE [STOPATMARKDB] TO DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Full.bak' WITH CHECKSUM, INIT, STATS=10

BEGIN TRAN MyTran1 WITH MARK 'Tran1'

INSERT INTO TestTable(DataValue) VALUES ('The Second Entry');

COMMIT TRAN MyTran1

BACKUP LOG [STOPATMARKDB] TO DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_1.trn' WITH CHECKSUM, INIT, STATS=10

INSERT INTO TestTable(DataValue) VALUES ('The Third Entry');

BEGIN TRAN MyTran2 WITH MARK 'Tran2'

INSERT INTO TestTable(DataValue) VALUES ('The Fourth Entry');

COMMIT TRAN MyTran2

BACKUP LOG [STOPATMARKDB] TO DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_2.trn' WITH CHECKSUM, INIT, STATS=10

INSERT INTO TestTable(DataValue) VALUES ('The Fifth Entry');

BACKUP LOG [STOPATMARKDB] TO DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_3.trn' WITH CHECKSUM, INIT, NO_TRUNCATE, STATS=10

/* check data */

SELECT * FROM dbo.TestTable

/* start restore process */

ALTER DATABASE [STOPATMARKDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE master

GO

RESTORE DATABASE [STOPATMARKDB] FROM DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Full.bak' WITH REPLACE, NORECOVERY, STATS=10

RESTORE LOG [STOPATMARKDB] FROM DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_1.trn' WITH STOPBEFOREMARK = 'MyTran1', STANDBY = N'C:\MSSQL\Backup\stndby.sbf', STATS=10

USE STOPATMARKDB

GO

/* only data from before the transaction is there STOPBEFOREMARK */

SELECT * FROM dbo.TestTable

USE MASTER

GO

RESTORE LOG [STOPATMARKDB] FROM DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_1.trn' WITH STOPATMARK = 'MyTran1', STANDBY = N'C:\MSSQL\Backup\stndby.sbf', STATS=10

USE STOPATMARKDB

GO

/* we stopped AT the mark now, so we have the data */

SELECT * FROM dbo.TestTable

USE MASTER

GO

/* finish any transaction from the end of Log_1.trn and stop BEFORE MyTran2 */

RESTORE LOG [STOPATMARKDB] FROM DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_1.trn' WITH STANDBY = N'C:\MSSQL\Backup\stndby.sbf', STATS=10

RESTORE LOG [STOPATMARKDB] FROM DISK = N'C:\MSSQL\Backup\STOPATMARKDB_Log_2.trn' WITH STOPBEFOREMARK = 'MyTran2', RECOVERY, STATS=10

GO

USE STOPATMARKDB

GO

/* everything before MyTran 2 */

SELECT * FROM dbo.TestTable

ALTER DATABASE STOPATMARKDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE master

GO

DROP DATABASE [STOPATMARKDB]