A typical scenario for using marked transactions includes the following steps:

1. Create a full or differential database backup of each of the related databases.
   
2. Mark a transaction block in all the databases.
   
3. Back up the transaction log for all the databases.
   
4. Restore database backups WITH NORECOVERY.
   
5. Restore logs WITH STOPATMARK.
   

Before inserting named marks into the transaction log, consider the following:

• Because transaction marks consume log space, use them only for transactions that play a significant role in the database recovery strategy.
  
• After a marked transaction commits, a row is inserted in the logmarkhistory table in msdb.
  
• If a marked transaction spans multiple databases on the same database server or on different servers, the marks must be recorded in the logs of all the affected databases.
  

To create a marked transaction, use the BEGIN TRANSACTION statement and the WITH MARK [description] clause. The optional description is a textual description of the mark. A mark name for the transaction is required. A mark name can be reused. The transaction log records the mark name, description, database, user, datetime information, and the log sequence number (LSN). The datetime information is used along with the mark name to uniquely identify the mark.

To create marked transactions in a set of databases:

1. Name the transaction in the BEGIN TRAN statement and use the WITH MARK clause
   You can nest the statement BEGIN TRAN new_mark_name WITH MARK within an existing transaction. The value of new_mark_name is the mark name for the transaction, even if the transaction possesses a transaction name.
   

   Note:
   If you issue a second nested BEGIN TRAN...WITH MARK, that statement is skipped but causes a warning message.

2. Run an update against all of the databases in the set.
   The mark for a specific transaction is inserted into transaction logs only on the server instance where the BEGIN TRAN...WITH MARK statement is executed. The transaction mark is placed in the transaction log of every database updated by the marked transaction on that server instance. If the databases reside on different server instances, identical marks must be created on each of the server instances.
   

Examples

USE AdventureWorks
GO
BEGIN TRANSACTION ListPriceUpdate
   WITH MARK 'UPDATE Product list prices';
GO

UPDATE Production.Product
   SET ListPrice = ListPrice * 1.10
   WHERE ProductNumber LIKE 'BK-%';
GO

COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database 
-- and log backups are taken.
-- An error occurs in the database.
USE master
GO

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 3, NORECOVERY;
GO

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups 
   WITH FILE = 4,
   RECOVERY, 
   STOPATMARK = 'ListPriceUpdate';

A transaction mark name is not automatically distributed to another server as the transaction spreads there. To force the mark to spread to the other servers, a stored procedure must be written that contains a BEGIN TRAN name WITH MARK statement. That stored procedure must then be executed on the remote server under the scope of the transaction in the originating server.

For example, consider a partitioned database that exists on multiple instances of SQL Server. On each instance is a database named coyote. First, in every database, create a stored procedure, for example, sp_SetMark.

CREATE PROCEDURE sp_SetMark
@name nvarchar (128)
AS
BEGIN TRANSACTION @name WITH MARK
UPDATE coyote.dbo.Marks SET one = 1
COMMIT TRANSACTION;
GO

Next, create stored procedure sp_MarkAll containing a transaction that places a mark in every database. sp_MarkAll can be run from any of the instances.

CREATE PROCEDURE sp_MarkAll
@name nvarchar (128)
AS
BEGIN TRANSACTION
EXEC instance0.coyote.dbo.sp_SetMark @name
EXEC instance1.coyote.dbo.sp_SetMark @name
EXEC instance2.coyote.dbo.sp_SetMark @name
COMMIT TRANSACTION;
GO

Two-Phase Commit

For information about how to recover a database that contains marked transactions to or just before a particular mark, see Recovering to a Marked Transaction.

Concepts

Other Resources

Help and Information