By Example – Marked Transactions
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
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.