復原包含標記之異動的相關資料庫Recovery of Related Databases That Contain Marked Transaction

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