• Article

Recovering to a Marked Transaction

This topic is relevant only for databases that contain marked transactions and that use the full or bulk-logged recovery models.

For information about the requirements for restoring to a specific recovery point, see Restoring a Database to a Point Within a Backup.

SQL Server supports inserting named marks into the transaction log to allow recovery to that specific mark. Log marks are transaction specific and are inserted only if their associated transaction commits. As a result, marks can be tied to specific work, and you can recover to a point that includes or excludes this work.

Before you insert 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. For more information, see Using Marked Transactions (Full Recovery Model).

Note

For information about how to mark transactions, see Using Marked Transactions (Full Recovery Model).

Transact-SQL Syntax for Inserting Named Marks into a Transaction Log

To insert marks into the transaction logs, use the BEGIN TRANSACTION statement and the WITH MARK [description] clause. The mark is named the same as the transaction. The optional description is a textual description of the mark, not the mark name. For example, the name of both the transaction and the mark that is created in the following BEGIN TRANSACTION statement is Tx1:

BEGIN TRANSACTION Tx1 WITH MARK 'not the mark name, just a description'

The transaction log records the mark name (transaction name), description, database, user, datetime information, and the log sequence number (LSN). The datetime information is used with the mark name to uniquely identify the mark.

For information about how to insert a mark into a transaction that spans multiple databases, see Using Marked Transactions (Full Recovery Model).

Transact-SQL Syntax for Recovering to a Mark

When you target a marked transaction by using a RESTORE LOG statement, you can use one the following clauses to stop at or immediately before the mark:

  • Use the WITH STOPATMARK = '<mark_name>' clause to specify that the marked transaction is the recovery point.

    STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward.

  • Use the WITH STOPBEFOREMARK = '<mark_name>' clause to specify that the log record that is immediately before the mark is the recovery point.

    STOPBEFOREMARK rolls forward to the mark and excludes marked the transaction from the roll forward.

The STOPATMARK and STOPBEFOREMARK options both support an optional AFTER datetime clause. When datetime is used, mark names do not have to be unique.

If AFTER datetime is omitted, roll forward stops at the first mark that has the specified name. If AFTER datetime is specified, roll forward stops at the first mark that has the specified name, exactly at or after datetime.

Note

As in all point-in-time restore operations, recovering to a mark is disallowed when the database is undergoing operations that are bulk-logged.

To restore to a marked transaction

How to: Restore a Database to a Marked Transaction (SQL Server Management Studio)

RESTORE (Transact-SQL)

Preparing the Log Backups

For this example, an appropriate backup strategy for these related databases would be the following:

  1. Use the full recovery model for both databases.

  2. Create a full backup of each database.

    The databases can be backed up sequentially or simultaneously.

  3. Before backing up the transaction log, mark a transaction that executes in all databases. For information about how to create the marked transactions, see Using Marked Transactions (Full Recovery Model).

  4. Back up the transaction log on each database.

Recovering the Database to a Marked Transaction

To restore the backup

  1. Create tail-log backups of the undamaged databases, if possible.

  2. Restore the most recent full database backup of each database.

  3. Identify the most recent marked transaction that is available in all of the transaction log backups. This information is stored in the logmarkhistory table in the msdb database on each server.

  4. Identify the log backups for all related databases that contain this mark.

  5. Restore each log backup, stopping at the marked transaction.

  6. Recover each database.