完整的資料庫還原 (完整復原模式)Complete Database Restores (Full Recovery Model)

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

在完整資料庫還原中,目標是還原整個資料庫。In a complete database restore, the goal is to restore the whole database. 在還原期間,整個資料庫為離線狀態。The whole database is offline for the duration of the restore. 在讓資料庫的任何部分上線之前,所有的資料都必須復原到一致的位置;此時資料庫的所有部分都會回到相同的時間點,而且沒有未認可的交易存在。Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.

在完整復原模式下,還原一個或多個資料備份之後,您必須先還原所有後續的交易記錄備份,然後再復原資料庫。Under the full recovery model, after you restore your data backup or backups, you must restore all subsequent transaction log backups and then recover the database. 您可以將資料庫還原到其中一個記錄備份內的特定 「復原點」 (Recovery point)。You can restore a database to a specific recovery point within one of these log backups. 復原點可以是特定日期和時間、標示的交易或記錄序號 (LSN)。The recovery point can be a specific date and time, a marked transaction, or a log sequence number (LSN).

還原資料庫時 (特別是在完整復原模式或大量記錄復原模式下),應該使用單一還原順序。When restoring a database, particularly under the full recovery model or bulk-logged recovery model, you should use a single restore sequence. 「還原順序」 (Restore sequence) 包含一個或多個還原作業,會在一個或多個還原階段中移動資料。A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore.

不受信任的來源Untrusted sources

建議您「不」 要附加或還原來自未知來源或不受信任之來源的資料庫。We recommend that you do not attach or restore databases from unknown or untrusted sources. 這些資料庫可能包含惡意程式碼,因此可能執行非預期的 Transact-SQLTransact-SQL 程式碼,或是修改結構描述或實體資料庫結構而造成錯誤。These databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 在您使用來自未知或不受信任之來源的資料庫時,請在非生產環境伺服器的資料庫上執行 DBCC CHECKDBBefore you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a non-production server. 此外,檢查資料庫中使用者撰寫的程式碼,如預存程序或其他使用者自訂的程式碼。Also, examine the user-written code in the database, such as stored procedures or other user-defined code.

來自舊版的備份Backups from earlier versions

如需舊版 SQL ServerSQL Server之備份支援的相關資訊,請參閱 RESTORE (Transact-SQL)的<相容性支援>一節。For information about support for backups from earlier versions of SQL ServerSQL Server, see the "Compatibility Support" section of RESTORE (Transact-SQL).

將資料庫還原到失敗點Restoring a Database to the Point of Failure

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

一般而言,將資料庫復原至失敗點的作業,包含下列基本步驟:Typically, recovering a database to the point of failure involves the following basic steps:

  1. 備份使用中的交易記錄檔 (即所謂的記錄檔結尾)。Back up the active transaction log (known as the tail of the log). 這會建立結尾記錄備份。This creates a tail-log backup. 如果沒有使用中的交易記錄,就會遺失該部分記錄中的所有交易。If the active transaction log is unavailable, all transactions in that part of the log are lost.


    在大量記錄復原模式下,備份包含大量記錄作業的記錄時,需要存取資料庫中的所有資料檔案。Under the bulk-logged recovery model, backing up any log that contains bulk-logged operations requires access to all data files in the database. 如果無法存取資料檔案,就無法備份交易記錄。If the data files cannot be accessed, the transaction log cannot be backed up. 在這種情況下,您必須手動重做最近一次備份記錄之後進行的所有變更。In that case, you have to manually redo all changes that were made since the most recent log backup.

    如需詳細資訊,請參閱結尾記錄備份 (SQL Server)For more information, see Tail-Log Backups (SQL Server).

  2. 還原最新的完整資料庫備份,但不復原資料庫 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  3. 如果有差異備份存在,請還原最新的備份,但是不要復原資料庫 (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY)。If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).

    還原最近一次的差異備份,可減少必須還原的記錄備份數目。Restoring the most recent differential backup reduces the number of log backups that must be restored.

  4. 從剛才還原的備份之後所建立的第一個交易記錄備份開始,依順序以 NORECOVERY 還原記錄。Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.

  5. 復原資料庫 (RESTORE DATABASE database_name WITH RECOVERY)。Recover the database (RESTORE DATABASE database_name WITH RECOVERY). 或者,這個步驟也可以合併在還原最後的記錄備份。Alternatively, this step can be combined with restoring the last log backup.

下圖顯示此還原順序。The following illustration shows this restore sequence. 在失敗發生之後 (1),會建立結尾記錄備份 (2)。After a failure occurs (1), a tail-log backup is created (2). 接下來,資料庫會還原至失敗點。Next, the database is restored to the point of the failure. 這個程序牽涉到還原資料庫備份、後續的差異備份,以及在差異備份之後所進行的每一個記錄備份,包括結尾記錄備份在內。This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.

完成資料庫還原至失敗的時間Complete database restore to the time of a failure


當您將資料庫備份還原至不同的伺服器執行個體時,請參閱 使用備份與還原複製資料庫When you restore a database backup onto a different server instance, see Copy Databases with Backup and Restore.

基本 Transact-SQL RESTORE 語法Basic Transact-SQL RESTORE Syntax

上圖中還原順序的基本 RESTORETransact-SQLTransact-SQL 語法如下:The basic RESTORETransact-SQLTransact-SQL syntax for the restore sequence in the preceding illustration is as follows:

  1. RESTORE DATABASE <database> FROM full database backup WITH NORECOVERY;RESTORE DATABASE database FROM full database backup WITH NORECOVERY;

  2. RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;


    針對其他每個記錄備份重複此還原記錄步驟。Repeat this restore-log step for each additional log backup.


範例:復原到失敗點 (Transact-SQL)Example: Recovering to the Point of Failure (Transact-SQL)

下列 Transact-SQLTransact-SQL 範例顯示將資料庫還原到失敗點之還原順序中的基本選項。The following Transact-SQLTransact-SQL example shows the essential options in a restore sequence that restores the database to the point of failure. 此範例會建立資料庫的結尾記錄備份。The example creates a tail-log backup of the database. 接下來,此範例會還原完整的資料庫備份和記錄備份,然後還原結尾記錄備份。Next, the example restores a full database backup and log backup and then restores the tail-log backup. 此範例會在一個不同的最後步驟中復原資料庫。The example recovers the database in a separate, final step.


此範例使用 完整資料庫備份 (SQL Server)的<相容性支援>一節。This example uses a database backup and log backup that is created in the "Using Database Backups Under the Full Recovery Model" section in Full Database Backups (SQL Server). 在資料庫備份之前, AdventureWorks2012AdventureWorks2012 範例資料庫會設定為使用完整復原模式。Before the database backup, the AdventureWorks2012AdventureWorks2012 sample database was set to use the full recovery model.

USE master;  
--Create tail-log backup.  
BACKUP LOG AdventureWorks2012   
TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'    
--Restore the full database backup (from backup set 1).  
RESTORE DATABASE AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   
  WITH FILE=1,   
--Restore the regular log backup (from backup set 2).  
RESTORE LOG AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   
  WITH FILE=2,   
--Restore the tail-log backup (from backup set 3).  
RESTORE LOG AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  
  WITH FILE=3,   
--recover the database:  

將資料庫還原到記錄備份內的時間點Restoring a Database to a Point Within a Log Backup

在完整復原模式下,完整資料庫還原通常可以復原到某個時間點、標示的交易或記錄備份內的 LSN。Under the full recovery model, a complete database restore can usually be recovered to a point of time, a marked transaction, or an LSN within a log backup. 然而,在大量記錄復原模式下,如果記錄備份包含大量記錄的變更,則無法進行時間點復原。However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible.

範例時間點還原案例Sample Point-in-Time Restore Scenarios

下列範例假設有一個關鍵任務的資料庫系統,每天午夜時會建立一次完整資料庫備份,從星期一到星期六每小時整點時會建立一次差異資料庫備份,而全天每隔 10 分鐘會建立一次交易記錄備份。The following example assumes a mission-critical database system for which a full database backup is created daily at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. 若要將資料庫還原至星期三 5:19 A.M. 時的狀態,To restore the database to the state is was in at 5:19 A.M. 請執行下列工作:Wednesday, do the following:

  1. 還原星期二午夜建立的完整資料庫備份。Restore the full database backup that was created Tuesday at midnight.

  2. 還原星期三上午 5:00 建立的差異資料庫備份Restore the differential database backup that was created at 5:00 A.M. on Wednesday.

  3. 套用星期三上午 5:10 建立的交易記錄備份,Apply the transaction log backup that was created at 5:10 A.M. on Wednesday.

  4. 套用星期三上午 5:20 建立的交易記錄備份,Apply the transaction log backup that was created 5:20 A.M. 並指定復原程序僅適用於上午 5:19 之前發生的交易。on Wednesday, specifying that the recovery process applies only to transactions that occurred before 5:19 A.M.

或者,如果需要將資料庫還原至星期四 3:04 A.M. 時的狀態,Alternatively, if the database needs to be restored to its state at 3:04 A.M. 但是無法使用星期四 3:00 A.M. 時建立的差異資料庫備份,Thursday, but the differential database backup that was created at 3:00 A.M. 則執行下列操作:Thursday is unavailable, do the following:

  1. 還原星期三午夜建立的資料庫備份。Restore the database backup that was created Wednesday at midnight.

  2. 還原星期四上午 2:00 建立的差異資料庫備份Restore the differential database backup that was created at 2:00 A.M. 建立的交易記錄備份。on Thursday.

  3. 套用星期四上午 2:10 到Apply all the transaction log backups created from 2:10 A.M. 變成上午 3:00。to 3:00 A.M. 建立的交易記錄備份。on Thursday.

  4. 套用星期四上午 3:10 建立的交易記錄備份,Apply the transaction log backup that was created at 3:10 A.M. 並在上午 3:04 停止復原程序。on Thursday, stopping the recovery process at 3:04 A.M.


如需特定時間點還原的範例,請參閱 將 SQL Server 資料庫還原至某個時間點 (完整復原模式)的<相容性支援>一節。For an example of a point-in-time restore, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

相關工作Related Tasks

還原完整資料庫備份To restore a full database backup

還原差異資料庫備份To restore a differential database backup

還原交易記錄備份To restore a transaction log backup

使用 SQL Server 管理物件 (SMO) 還原備份To restore a backup by using SQL Server Management Objects (SMO)

將資料庫還原到記錄備份內的時間點To restore a database to a point within a log backup

另請參閱See Also

RESTORE (Transact-SQL) RESTORE (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
套用交易記錄備份 (SQL Server) Apply Transaction Log Backups (SQL Server)
sp_addumpdevice (Transact-SQL) sp_addumpdevice (Transact-SQL)
完整資料庫備份 (SQL Server) Full Database Backups (SQL Server)
差異備份 (SQL Server) Differential Backups (SQL Server)
備份概觀 (SQL Server) Backup Overview (SQL Server)
還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)