Marked transactions are used to restore related databases to a logical consistent recovery point (A good example is Team Foundation Server). The databases have to be in Full or Bull logging recovery model. Note that MSDN states that Point in Time restores are not sufficient for this purpose.

Create Transaction which are Marked

To create a marked transaction you need to name the transaction during the BEGIN TRANSACTION phase of the statement, the example below creates a marked transaction called myTran:

BEGIN TRAN myTran WITH MARK
UPDATE dbo.Tbl_TransactionLogMark SET logmark = 2
COMMIT TRAN
GO

Restore using Marked Transactions

To restore to a marked transaction you need to use the STOPATMARK. If you use the same name for the transaction you will need to use the AFTER clause to differentiate from each marked transaction (Use msdb..logmarkhistory to get the time).

RESTORE DATABASE [tfs_test1] 
FROM DISK = N'C:\SQL_BACKUP\test1_FULL.bak'
WITH NORECOVERY, REPLACE
GO

-- NOTE THE USE OF AFTER TO RECOVERY WHEN MULTIPLE same named trans exist.

RESTORE LOG [tfs_test1] 
FROM DISK = N'C:\SQL_BACKUP\tfs_test1_LOG.trn'
WITH RECOVERY, STOPATMARK = 'myTran' AFTER '2014-09-12 11:46:50.037'
GO

Querying Information on Marked Transactions

You can view marked transaction for all databases on the instance in the MSDB table logmarkhistory

-- View Marked Transaction Log in MSDB
SELECT * FROM msdb..logmarkhistory

MSDN Link:
More Information

Maintenance of table msdb..logmarkhistory

Msdb..backupset has a delete on trigger (trig_backupset_delete) which removes any marked transaction from logmarkhistory that is related to the LSNs being removed.

Advertisements