包含标记事务的相关数据库恢复Recovery of Related Databases That Contain Marked Transaction

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题仅适用于那些包含标记事务和使用完整或大容量日志恢复模式的数据库。This topic is relevant only for databases that contain marked transactions and that use the full or bulk-logged recovery models.

有关还原到特定恢复点的要求的详细信息,请参阅 将 SQL Server 数据库还原到某个时间点(完整恢复模式)For information about the requirements for restoring to a specific recovery point, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

SQL ServerSQL 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.

  • 标记的事务提交之后,在 msdblogmarkhistory表中插入一行。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 Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

在事务日志中插入命名标记的 Transact-SQL 语法Transact-SQL Syntax for Inserting Named Marks into a Transaction Log

若要将标记插入到事务日志中,请使用 BEGIN TRANSACTION 语句和 WITH MARK [description] 子句。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. 可选的 description 是标记的文本说明而不是标记名。The optional description is a textual description of the mark, not the mark name. 例如,在以下 BEGIN TRANSACTION 语句中创建的事务和标记的名称均为 Tx1For 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'    

事务日志记录了标记名(事务名)、说明、数据库、用户、 datetime 信息和日志序列号 (LSN)。The transaction log records the mark name (transaction name), description, database, user, datetime information, and the log sequence number (LSN). datetime 信息与标记名一起用于唯一地标识标记。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 Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model).

恢复到某个标记的 Transact-SQL 语法Transact-SQL Syntax for Recovering to a Mark

使用RESTORE LOG语句将某个标记的事务作为目标时,可以使用以下子句之一在标记处或在紧邻其之前的位置处停止:When you target a marked transaction by using aRESTORE LOGstatement, you can use one the following clauses to stop at or immediately before the mark:

  • 使用 WITH STOPATMARK = ' <mark_name> ' 子句将标记事务指定为恢复点。Use the WITH STOPATMARK = '<mark_name>' clause to specify that the marked transaction is the recovery point.

    STOPATMARK 前滚到标记处,并在前滚中包含标记的事务。STOPATMARK rolls forward to the mark and includes the marked transaction in the roll forward.

  • 使用 WITH STOPBEFOREMARK = ' <mark_name> ' 子句将紧邻标记之前的日志记录指定为恢复点。Use the WITH STOPBEFOREMARK = '<mark_name>' clause to specify that the log record that is immediately before the mark is the recovery point.

    STOPBEFOREMARK 前滚到标记处,但在前滚中不包含标记的事务。STOPBEFOREMARK rolls forward to the mark and excludes marked the transaction from the roll forward.

STOPATMARK 和 STOPBEFOREMARK 选项均支持可选的 AFTER datetime 子句。The STOPATMARK and STOPBEFOREMARK options both support an optional AFTER datetime clause. 使用 datetime 时,标记名不必是唯一的。When datetime is used, mark names do not have to be unique.

如果省略了 AFTER datetime ,则前滚操作将于达到含有指定名称的第一个标记处停止。If AFTER datetime is omitted, roll forward stops at the first mark that has the specified name. 如果指定了 AFTER datetime ,则前滚操作将于达到 datetime时或之后在具有指定名称的第一个标记处停止。If AFTER datetime is specified, roll forward stops at the first mark that has the specified name, exactly at or after datetime.

备注

对于所有时点还原操作而言,如果数据库正在执行成批记录的操作,则不允许恢复到标记。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

将数据库还原到标记的事务 (SQL Server Management Studio)Restore a Database to a Marked Transaction (SQL Server Management Studio)

RESTORE (Transact-SQL)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 Use Marked Transactions to Recover Related Databases Consistently (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. 此信息存储在每个服务器的 msdb 数据库中的 logmarkhistory 表中。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.

另请参阅See Also

BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
应用事务日志备份 (SQL Server) Apply Transaction Log Backups (SQL Server)
使用标记的事务一致地恢复相关的数据库的事务(完全恢复模式) Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model)
还原和恢复概述 (SQL Server) Restore and Recovery Overview (SQL Server)
将 SQL Server 数据库还原到某个时间点(完整恢复模式) Restore a SQL Server Database to a Point in Time (Full Recovery Model)
计划和执行还原顺序(完整恢复模式)Plan and Perform Restore Sequences (Full Recovery Model)