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]
