Recovering to a Named Transaction

Microsoft® SQL Server™ 2000 supports the insertion of named marks into the transaction log to allow recovery to that specific mark. Log marks are transactional 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 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.
  
  
• For each marked transaction that 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 Backup and Recovery of Related Databases.

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. Because the name of the mark is the same as its transaction, a transaction name is required. The optional description is a textual description of the mark.

The transaction log records the mark name, description, database, user, datetime information, and the Log Sequence Number (LSN). To allow their reuse, the transaction names are not required to be unique. The datetime information is used along with the name to uniquely identify the mark.

Recovering to a Mark

There are two ways to recover to a mark in the log:

• Use RESTORE LOG and the WITH STOPATMARK='mark_name' clause to roll forward to the mark and include the transaction that contains the mark.
  
  
• Use RESTORE LOG and the WITH STOPBEFOREMARK='mark_name' clause to roll forward to the mark and exclude the transaction that contains the mark.

The WITH STOPATMARK and WITH STOPBEFOREMARK clauses support an optional AFTER datetime clause. If AFTER datetime is omitted, recovery stops at the first mark with the specified name. If AFTER datetime is specified, recovery stops at the first mark with the specified name on or after datetime.

Note  Recovering to a mark is subject to the same restrictions as point-in-time recovery. Specifically, recovering to a mark is disallowed during intervals in which the database is undergoing operations that are bulk-logged.