套用異動記錄備份 (SQL Server)Apply Transaction Log Backups (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題僅與完整復原模式和大量記錄復原模式有關。The topic is relevant only for the full recovery model or bulk-logged recovery model.

此主題描述如何在還原 SQL ServerSQL Server 資料庫的過程中套用交易記錄備份。This topic describes applying transaction log backups as part of restoring a SQL ServerSQL Server database.

還原交易記錄備份的需求Requirements for restoring transaction log backups

若要套用交易記錄備份,必須符合下列需求:To apply a transaction log backup, the following requirements must be met:

  • 有足夠的記錄備份供還原順序使用: 您必須備份了足夠的記錄,才能完成還原順序。Enough Log Backups for a Restore Sequence : You must have enough log records backed up to complete a restore sequence. 您必須先備妥必要的記錄備份,包括所需的 結尾記錄備份 ,才開始還原順序。The necessary log backups, including the tail-log backup where required, must be available before the start of the restore sequence.

  • 正確的還原順序: 首先必須還原最新的完整資料庫備份或差異資料庫備份。Correct restore order: The immediately previous full database backup or differential database backup must be restored first. 接著,必須依時間先後順序,還原在該完整或差異資料庫備份之後建立的所有交易記錄。Then, all transaction logs that are created after that full or differential database backup must be restored in chronological order. 如果這個記錄鏈結中的某個交易記錄備份遺失或損毀,您只能還原該遺漏的交易記錄之前的交易記錄。If a transaction log backup in this log chain is lost or damaged, you can restore only transaction logs before the missing transaction log.

  • 資料庫尚未復原: 直到套用了最後一個交易記錄之後,才能復原資料庫。Database not yet recovered: The database cannot be recovered until after the final transaction log has been applied. 如果您只還原了其中一個中繼交易記錄備份 (尚未到達記錄鏈結的尾端) 之後便復原資料庫,則您無法還原該時間點之後的資料庫,除非您以完整資料庫備份開始重新啟動整個還原順序。If you recover the database after restoring one of the intermediate transaction log backups, that before the end of the log chain, you cannot restore the database past that point without restarting the complete restore sequence, starting with the full database backup.

    提示!TIP! 最佳做法就是還原所有的記錄備份 (RESTORE LOG 資料庫名稱 WITH NORECOVERY)。A best practice is to restore all the log backups (RESTORE LOG database_name WITH NORECOVERY). 然後,在還原最後一個記錄備份之後,以個別的作業來復原資料庫 (RESTORE DATABASE 資料庫名稱 WITH RECOVERY)。Then, after restoring the last log backup, recover the database in a separate operation (RESTORE DATABASE database_name WITH RECOVERY).

復原與交易記錄Recovery and transaction logs

當您完成還原作業並復原資料庫時,復原作業會回復所有未完成的交易。When you finish the restore operation and recover the database, recovery rolls back all incomplete transactions. 此即稱為 「恢復階段」This is known as the undo phase. 需要進行回復,才能還原資料庫的完整性。Rolling back is required to restore the integrity of the database. 回復之後,資料庫會上線,而且不再有交易記錄備份可以套用到資料庫。After rollback, the database goes online, and no more transaction log backups can be applied to the database.

例如,一系列交易記錄備份中包含長時間執行的交易。For example, a series of transaction log backups contain a long-running transaction. 該交易的開頭記錄在第一個交易記錄備份,但是交易的結尾記錄在第二個交易記錄備份。The start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. 那麼一個交易記錄備份中將沒有認可或回復作業的記錄。There is no record of a commit or rollback operation in the first transaction log backup. 如果在套用第一個交易記錄備份時執行復原作業,則會將長時間執行的交易視為未完成,並且回復在交易的第一個交易記錄備份中記錄的資料修改。If a recovery operation runs when the first transaction log backup is applied, the long-running transaction is treated as incomplete, and data modifications recorded in the first transaction log backup for the transaction are rolled back. SQL ServerSQL Server 不允許在此時間點之後套用第二個交易記錄備份。does not allow for the second transaction log backup to be applied after this point.

注意: 在某些狀況下,您可以在記錄還原期間明確加入檔案。NOTE: In some circumstances, you can explicitly add a file during log restore.

使用記錄備份還原到失敗點Use log backups to restore to the failure point

假設發生下列事件順序。Assume the following sequence of events.

TimeTime 事件Event
上午 8:008:00 A.M. 備份資料庫以建立完整資料庫備份。Back up database to create a full database backup.
中午Noon 備份交易記錄。Back up transaction log.
下午 4:004:00 P.M. 備份交易記錄。Back up transaction log.
下午 6:006:00 P.M. 備份資料庫以建立完整資料庫備份。Back up database to create a full database backup.
下午 8:008:00 P.M. 備份交易記錄。Back up transaction log.
下午 9:459:45 P.M. 發生故障。Failure occurs.

如需這個備份順序範例的說明,請參閱交易記錄備份 (SQL Server)For an explanation of this example sequence of backups, see Transaction Log Backups (SQL Server).

若要將資料庫還原至下午 9:45 的狀態To restore the database to its state at 9:45 P.M. (失敗點),您可以使用下列任何一個替代程序:(the point of failure), either of the following alternative procedures can be used:

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。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.

替代程序 1:使用最新的完整資料庫備份來還原資料庫Alternative 1: Restore the database by using the most recent full database backup

  1. 將目前使用者中交易記錄的結尾記錄備份建立為失敗點。Create a tail-log backup of the currently active transaction log as of the point of failure.

  2. 不要還原上午 8:00 的Do not restore the 8:00 A.M. 完整資料庫備份進行還原,這個程序需要更多的時間。full database backup. 而是還原下午 6:00 的最新Instead, restore the more recent 6:00 P.M. 完整資料庫備份,然後套用下午 8:00 的full database backup, and then apply the 8:00 P.M. 記錄備份及結尾記錄備份。log backup and the tail-log backup.

替代程序 2:使用較早的完整資料庫備份來還原資料庫Alternative 2: Restore the database by using an earlier full database backup

如果發生問題,讓您無法使用下午 6:00 的This alternative process is useful if a problem prevents you from using the 6:00 P.M. 完整資料庫備份進行還原,這個程序需要更多的時間。full database backup. 比起從下午 6:00 的This process takes longer than restoring from the 6:00 P.M. 完整資料庫備份進行還原,這個程序需要更多的時間。full database backup.

  1. 將目前使用者中交易記錄的結尾記錄備份建立為失敗點。Create a tail-log backup of the currently active transaction log as of the point of failure.

  2. 還原上午 8:00 的Restore the 8:00 A.M. 完整資料庫備份,然後依序還原全部四個交易記錄備份。full database backup, and then restore all four transaction log backups in sequence. 如此即可將下午 9:45 前完成的所有交易都向前復原。This rolls forward all completed transactions up to 9:45 P.M.

    這個替代程序指出維護一系列完整資料庫備份的交易記錄備份鏈結可以提供的額外安全性。This alternative points out the redundant security offered by maintaining a chain of transaction log backups across a series of full database backups.

在某些情況下,您也可以使用交易記錄,將資料庫還原到特定時間點。In some cases, you can also use transaction logs to restore a database to a specific point in time. 如需詳細資訊,請參閱 將 SQL Server 資料庫還原至某個時間點 (完整復原模式)For more information, Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Related tasksRelated tasks

套用交易記錄備份To apply a transaction log backup

還原到您的復原點To restore to your recovery point

若要使用 WITH NORECOVERY,在還原備份之後復原資料庫To recover a database after restoring backups using WITH NORECOVERY

另請參閱See also

交易記錄 (SQL Server)The Transaction Log (SQL Server)