使用標示的交易以一致的方式復原相關資料庫Use Marked Transactions to Recover Related Databases Consistently

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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.

  5. 使用 WITH STOPATMARK 還原記錄。Restore logs WITH STOPATMARK.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用標示的交易的考量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 [描述 ] 子句。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. 交易記錄中會記錄標示名稱、描述、資料庫、使用者、日期時間資訊與記錄序號 (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 name 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_MarkAll ,它會在每一個資料庫中放入標示。Next, 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