使用标记的事务一致地恢复相关的数据库Use Marked Transactions to Recover Related Databases Consistently

适用于: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

本主题仅与使用完整恢复模式或大容量日志恢复模式的 SQL ServerSQL Server 数据库相关。This topic is relevant only for SQL ServerSQL Server databases that are using the full or bulk-logged recovery models.

在对两个或更多数据库(“相关数据库” )进行相关更新时,可以使用事务标记将它们恢复到逻辑上一致的点。When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. 但是,此恢复将丢失在作为恢复点的标记之后提交的所有事务。However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. 只有您在测试相关数据库或不介意丢失近期提交的事务时,标记事务才适用。Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

在每个相关数据库中定期标记相关事务将在数据库中建立一系列公用恢复点。Routinely marking related transactions in every related database establishes a series of common recovery points in the databases. 事务标记将记录在事务日志中并包括在日志备份中。The transaction marks are recorded in the transaction log and included in log backups. 发生灾难时,可以将各数据库还原到相同的事务标记,从而将它们恢复到一致的点。In the event of a disaster, you can restore each of the databases to the same transaction mark to recover them to a consistent point.


可以对不同的数据库独立创建各日志备份,而无需同时创建。Log backups on the different databases can be created independently of each other and do not have to be simultaneous.

对于以下情况,若要恢复相关数据库,则必须先对各相关数据库中的事务进行标记:Recovering related databases in the following scenarios requires that you have already marked transactions in every related database:

  • 一个或多个事务日志已破坏。One or more transaction logs are destroyed. 需要将数据库集还原到上次进行日志备份时的一致状态。You have to restore the set of databases to a consistent state at the time of your last log backup.

  • 需要将整个数据库集还原到某个更早时间点的相互一致状态。You have to restore the entire set of databases to a mutually consistent state at some earlier point in time.


您仅可以将相关数据库恢复为标记事务,而不是特定时间点。You can recover related databases only to a marked transaction, not to a specific point in time.

有关如何创建标记事务的信息,请参阅本主题后面的“创建标记事务”。For information about how to create marking transactions, see "Creating the Marked Transactions," later in this topic.

使用标记事务的典型方案Typical Scenario for Using Marked Transactions

使用标记事务的典型方案包括以下步骤: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. 用 WITH NORECOVERY 还原数据库备份。Restore database backups WITH NORECOVERY.


使用标记事务的注意事项Considerations for Using Marked Transactions

在将已命名的标记插入事务日志之前,请注意以下事项: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.

  • 标记的事务提交之后,在 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.

创建标记事务Creating the Marked Transactions

若要创建标记事务,请使用 BEGIN TRANSACTION 语句和 WITH MARK [description] 子句。To create a marked transaction, use the BEGIN TRANSACTION statement and the WITH MARK [description] clause. 可选内容 description 是关于标记的文字说明。The optional description is a textual description of the mark. 必须为事务指定标记名称。A mark name for the transaction is required. 标记名称可以重复使用。A mark name can be reused. 事务日志记录标记名称、描述、数据库、用户、日期时间信息以及日志序列号 (LSN)。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. 用 BEGIN TRAN 语句命名事务,并使用 WITH MARK 子句。Name the transaction in the BEGIN TRAN statement and use the WITH MARK clause

    你可以在现有事务中嵌套 BEGIN TRAN new_mark_name WITH MARK 语句。You can nest the statement BEGIN TRAN new_mark_name WITH MARK within an existing transaction. 即使事务拥有事务名称, new_mark_name 的值也是事务的标记名称。The value of new_mark_name is the mark name for the transaction, even if the transaction possesses a transaction name.


    如果执行第二个嵌套 BEGIN TRAN...WITH MARK,那么将跳过该语句,但会导致出现警告消息。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.

    将仅在执行 BEGIN TRAN...WITH MARK 语句的服务器实例上的事务日志中插入特定事务的标记。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.


下面的示例将事务日志还原到名为 ListPriceUpdate的标记事务中的标记处。The following example restores the transaction log to the mark in the marked transaction named ListPriceUpdate.

USE AdventureWorks  
   WITH MARK 'UPDATE Product list prices';  
UPDATE Production.Product  
   SET ListPrice = ListPrice * 1.10  
   WHERE ProductNumber LIKE 'BK-%';  
-- Time passes. Regular database   
-- and log backups are taken.  
-- An error occurs in the database.  
USE master  
FROM AdventureWorksBackups  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups   
   WITH FILE = 4,  
   STOPATMARK = 'ListPriceUpdate';  

强制将标记分布到其他服务器Forcing a Mark to Spread to Other Servers

事务分散到其他服务器时,事务标记名称不会自动分布到其他服务器。A transaction mark name is not automatically distributed to another server as the transaction spreads there. 若要强制标记分散到其他服务器,必须编写包含 BEGIN TRAN 名称 WITH MARK 语句的存储过程。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.

例如,假设多个 SQL ServerSQL Server实例上存在分区数据库。For example, consider a partitioned database that exists on multiple instances of SQL ServerSQL Server. 每个实例上都有一个名为 coyote的数据库。On each instance is a database named coyote. 首先,在每个数据库中创建一个存储过程,例如, sp_SetMarkFirst, in every database, create a stored procedure, for example, sp_SetMark.

@name nvarchar (128)  
UPDATE coyote.dbo.Marks SET one = 1  

然后,创建包含在每个数据库中放置标记的事务的存储过程 sp_MarkAllNext, create stored procedure sp_MarkAll containing a transaction that places a mark in every database. sp_MarkAll 可以从任意实例中运行。sp_MarkAll can be run from any of the instances.

@name nvarchar (128)  
EXEC instance0.coyote.dbo.sp_SetMark @name  
EXEC instance1.coyote.dbo.sp_SetMark @name  
EXEC instance2.coyote.dbo.sp_SetMark @name  

两阶段提交Two-Phase Commit

提交分布式事务分为两个阶段:准备和提交。Committing a distributed transaction occurs in two phases: prepare and commit. 提交标记事务时,标记事务中每个数据库的提交日志记录都放在日志的某一点处,以使任何日志中不存在有疑问的事务。When a marked transaction is committed, the commit log record for each database in the marked transaction is placed in the log at a point where there are no in-doubt transactions in any of the logs. 此点保证不存在这样的事务:在一个日志中显示为已提交,在另一个日志中显示为未提交。At this point, it is guaranteed that there are no transactions that appear as committed in one log, but not committed in another log.

为此,可在提交标记事务的过程中执行下列操作:The following steps accomplish this during the commit of a marked transaction:

  1. 标记事务的准备阶段停止所有新的准备和提交。Prepare phase of a marking transaction stalls all new prepares and commits.

  2. 仅允许继续提交准备好的事务。Only commits of already prepared transactions are allowed to continue.

  3. 标记事务将等待耗尽所有准备好的事务(带超时设置)。Marking transaction then waits for all prepared transactions to drain (with time-out).

  4. 准备并提交标记事务。Marked transaction is prepared and committed.

  5. 取消停止新的准备和提交。The stall of new prepares and commits is removed.

由跨多个数据库的标记事务产生的停止会降低服务器的事务处理性能。The stalls generated by marked transactions that span multiple databases can reduce the transaction processing performance of the server.

建议不要运行并发标记事务。We recommend that you do not run concurrent marked transactions. 虽然很少见,但分布式标记事务有可能与其他同时提交的分布式标记事务发生死锁。It is rare but possible for the commit of a distributed marked transaction to deadlock with other distributed marked transactions that are committing at the same time. 出现这种情况时,正在标记的事务将被选作死锁牺牲品并回滚。When this happens, the marking transaction is chosen as the deadlock victim and is rolled back. 出现此错误时,应用程序会重试标记事务。When this error occurs, the application can retry the marked transaction. 多个标记事务尝试同时提交时,发生死锁的可能性较大。When multiple marked transactions try to commit concurrently, there is a higher probability of deadlock.

恢复到标记的事务Recovering to a Marked Transaction

有关如何将包含标记事务的数据库恢复到特定标记或仅恢复到特定标记之前,请参阅 包含标记事务的相关数据库恢复For information about how to recover a database that contains marked transactions to or just before a particular mark, see Recovery of Related Databases That Contain Marked Transaction.

另请参阅See Also

备份和还原系统数据库 (SQL Server) Back Up and Restore of System Databases (SQL Server)
应用事务日志备份 (SQL Server) Apply Transaction Log Backups (SQL Server)
完整数据库备份 (SQL Server) Full Database Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
包含标记的事务的相关数据库的恢复Recovery of Related Databases That Contain Marked Transaction