將資料庫還原到失敗點 - 完整復原Restore Database to Point of Failure - Full Recovery

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

此主題說明如何還原到失敗點。This topic explains how to restore to the point of failure. 此主題只與使用完整或大量記錄復原模式的資料庫有關。The topic is relevant only for databases that are using the full or bulk-logged recovery models.

還原到失敗點To restore to the point of failure

  1. 執行下列基本的 BACKUP 陳述式,備份記錄的結尾:Back up the tail of the log by running the following basic BACKUP statement:

    BACKUP LOG <database_name> TO <backup_device>   
       WITH NORECOVERY, NO_TRUNCATE;  
    
  2. 執行下列基本的 RESTORE DATABASE 陳述式,還原完整的資料庫備份:Restore a full database backup by running the following basic RESTORE DATABASE statement:

    RESTORE DATABASE <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  3. 也可以選擇,執行下列基本的 RESTORE DATABASE 陳述式,還原差異資料庫備份:Optionally, restore a differential database backup by running the following basic RESTORE DATABASE statement:

    RESTORE DATABASE <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  4. 套用每個交易記錄,包括您在步驟 1,透過在 RESTORE LOG 陳述式中指定 WITH NORECOVERY 所建立的結尾記錄備份:Apply each transaction log, including the tail-log backup you created in step 1, by specifying WITH NORECOVERY in the RESTORE LOG statement:

    RESTORE LOG <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  5. 執行下列 RESTORE DATABASE 陳述式,復原資料庫:Recover the database by running the following RESTORE DATABASE statement:

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

```  
RESTORE DATABASE <database_name>   
   WITH RECOVERY;  
```  

範例Example

在執行範例之前,必須先完成下列準備工作:Before you can run the example, you must complete the following preparations:

  1. AdventureWorks2012AdventureWorks2012 資料庫的預設復原模式是簡單復原模式。The default recovery model of the AdventureWorks2012AdventureWorks2012 database is the simple recovery model. 由於此復原模式不支援還原至失敗點,請執行下列 AdventureWorks2012AdventureWorks2012 ALTER DATABASE 陳述式,設定 使用完整復原模式:Because this recovery model does not support restoring to the point of a failure, set AdventureWorks2012AdventureWorks2012 to use the full recovery model by running the following ALTER DATABASE statement:

    USE master;  
    GO  
    ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  
    
  2. 使用下列 BACKUP 陳述式,建立資料庫的完整資料庫備份:Create a full database back of the database by using the following BACKUP statement:

    BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Data.bck';  
    
  3. 建立例行的記錄備份:Create a routine log backup:

    BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck';  
    

下列範例會在建立 AdventureWorks2012AdventureWorks2012 資料庫的結尾記錄備份之後,還原先前建立的備份The following example restores the backups that are created previously, after creating a tail-log backup of the AdventureWorks2012AdventureWorks2012 database. (此步驟假定可以取得記錄磁碟)。(This step assumes that the log disk can be accessed.)

首先,範例建立資料庫的結尾記錄備份,擷取使用中的記錄,並讓資料庫保留為「還原」狀態。First, the example creates a tail-log backup of the database that captures the active log and leaves the database in the Restoring state. 然後,範例還原資料庫備份,套用先前建立的例行記錄備份,然後套用結尾記錄備份。Then, the example restores the database backup, applies the routine log backup created previously, and applies the tail-log backup. 最後,範例用另外一個步驟復原資料庫。Finally, the example recovers the database in a separate step.

注意

預設行為是復原資料庫,作為還原最後備份的陳述式一部分。The default behavior is to recover a database as part of the statement that restores the final backup.

/* Example of restoring a to the point of failure */  
-- Step 1: Create a tail-log backup by using WITH NORECOVERY.  
BACKUP LOG AdventureWorks2012  
   TO DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 2: Restore the full database backup.  
RESTORE DATABASE AdventureWorks2012  
   FROM DISK = 'C:\AdventureWorks2012_Data.bck'  
   WITH NORECOVERY;  
GO  
-- Step 3: Restore the first transaction log backup.  
RESTORE LOG AdventureWorks2012  
   FROM DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 4: Restore the tail-log backup.  
RESTORE LOG AdventureWorks2012  
   FROM  DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 5: Recover the database.  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)