檔案還原 (完整復原模式)File Restores (Full Recovery Model)

本主題適用於:是SQL Server (從 2016 年起)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

這個主題僅與在完整或大量載入復原模式下,包含多個檔案或檔案群組的資料庫有關。This topic is relevant only for databases that contain multiple files or filegroups under the full or bulk-load recovery model.

檔案還原的目的是還原一個或多個損毀的檔案,而不還原整個資料庫。In a file restore, the goal is to restore one or more damaged files without restoring the whole database. 檔案還原實例包含複製、向前復原及復原適當資料的單一還原順序。A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data

如果正在還原的檔案群組為可讀寫,則必須在還原最後一個資料或差異備份之後,套用無間斷的記錄備份鏈結。If the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. 這會將檔案群組向前復原到位於記錄檔內目前使用中記錄檔記錄上的記錄檔記錄。This brings the filegroup forward to the log records in the current active log records in the log file. 復原點通常接近記錄的結尾,但不一定如此。The recovery point is typically near the end of log, but not necessarily.

如果正在還原的檔案群組為唯讀,通常沒有必要套用記錄備份,所以會略過。If the filegroup that is being restored is read-only, usually applying log backups is unnecessary and is skipped. 如果是在檔案變成唯讀之後進行的備份,此備份就是要還原的最後一個備份。If the backup was taken after the file became read-only, that is the last backup to restore. 向前復原會在目標點停止。Roll forward stops at the target point.

這些檔案還原實例如下:The file-restore scenarios are as follows:

  • 離線檔案還原Offline file restore

    「離線檔案還原」(Offline File Restore) 中,還原損毀的檔案或檔案群組時,資料庫處於離線狀態。In an offline file restore, the database is offline while damaged files or filegroups are restored. 在還原順序結束後,資料庫會恢復上線。At the end of the restore sequence, the database comes online.

    SQL Server 2016SQL Server 2016 的所有版本都支援離線檔案還原。All editions of SQL Server 2016SQL Server 2016 support offline file restore.

  • 線上檔案還原Online file restore

    「線上檔案還原」(Online File Restore) 中,如果資料庫在還原期間處於線上,則在檔案還原期間也會處於線上。In an online file restore, if database is online at restore time, it remains online during the file restore. 不過,在還原作業期間,包含正在還原之檔案的每個檔案群組都會離線。However, each filegroup in which a file is being restored is offline during the restore operation. 離線檔案群組中的所有檔案都復原後,檔案群組就會自動回到線上。After all the files in an offline filegroup are recovered, the filegroup is automatically brought online.

    如需線上頁面和檔案還原支援的資訊,請參閱 SQL Server 2016 版本支援的功能For information about support for online page and file restore, see Editions and Supported Features for SQL Server 2016. 如需線上還原的詳細資訊,請參閱線上還原 (SQL Server)For more information about online restores, see Online Restore (SQL Server).

    提示

    若您要讓資料庫離線以進行檔案還原,請在啟動還原順序之前,執行下列 ALTER DATABASE 陳述式:ALTER DATABASE database_name SET OFFLINE。If you want the database to be offline for a file restore, take the database offline before you start the restore sequence by executing the following ALTER DATABASE statement: ALTER DATABASE database_name SET OFFLINE.

從檔案備份還原損壞的檔案 Restoring Damaged Files from File Backups

  1. 在還原一個或多個損壞的檔案之前,請嘗試建立 結尾記錄備份Before restoring one or more damaged files, attempt to create a tail-log backup.

    如果記錄已經損壞,就無法建立結尾記錄備份,而且您必須還原整個資料庫。If the log has been damaged, a tail-log backup cannot be created, and you must restore the whole database.

    如需如何備份交易記錄的相關資訊,請參閱交易記錄備份 (SQL Server)For information about how to back up a transaction log, see Transaction Log Backups (SQL Server).

    重要

    如果是離線檔案還原,一定要在檔案還原之前進行結尾記錄備份。For an offline file restore, you must always take a tail-log backup before the file restore. 如果是線上檔案還原,一定要在檔案還原之後進行記錄備份。For an online file restore, you must always take the log backup after the file restore. 為了讓檔案可以復原到與資料庫其餘部分一致的狀態,進行這個記錄備份有其必要。This log backup is necessary to allow for the file to be recovered to a state consistent with the rest of the database.

  2. 從每個損毀檔案的最近一次檔案備份還原該檔案。Restore each damaged file from the most recent file backup of that file.

  3. 還原每個已還原檔案的最新差異檔案備份 (如果有的話)。Restore the most recent differential file backup, if any, for each restored file.

  4. 依序還原交易記錄備份,從包含最舊還原檔案的備份開始,到步驟 1 所建立的結尾記錄備份結束。Restore transaction log backups in sequence, starting with the backup that covers the oldest of the restored files and ending with the tail-log backup created in step 1.

    您必須還原在檔案備份之後建立的交易記錄備份,才能讓資料庫恢復一致的狀態。You must restore the transaction log backups that were created after the file backups to bring the database to a consistent state. 交易記錄備份可以快速地向前復原,因為只需套用適用於還原檔案的變更。The transaction log backups can be rolled forward quickly, because only the changes that apply to the restored files are applied. 還原個別檔案比還原整個資料庫更為理想,因為不需複製未受損的檔案,便可接著向前復原。Restoring individual files can be better than restoring the whole database, because undamaged files are not copied and then rolled forward. 不過,仍然需要讀取記錄備份的整個鏈結。However, the whole chain of log backups still has to be read.

  5. 復原資料庫。Recover the database.

注意

檔案備份可以用來將資料庫還原至較早的時間點。File backups can be used to restore the database to an earlier point in time. 若要這樣做,您必須還原整個檔案備份組,然後依序還原交易記錄備份,以回到上一次還原的檔案備份結尾之後的目標時間點。To do this, you must restore a complete set of file backups, and then restore transaction log backups in sequence to reach a target point that is after the end of the most recent restored file backup. 如需時間點還原的詳細資訊,請參閱將 SQL Server 資料庫還原至某個時間點 (完整復原模式)For more information about point-in-time recovery, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

離線檔案還原的 Transact-SQL 還原順序 (完整復原模式)Transact-SQL Restore Sequence for an Offline File Restore (Full Recovery Model)

檔案還原實例包含複製、向前復原及復原適當資料的單一還原順序。A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data.

本節說明檔案還原順序的基本 RESTORE 選項。This section shows the essential RESTORE options for a file-restore sequence. 會省略與這個檔案還原無關的語法和詳細資料。Syntax and details that are not relevant to this purpose are omitted.

下列範例還原順序顯示如何使用 WITH NORECOVERY 來離線還原兩個次要檔案 ( AB)。The following sample restore sequence shows an offline restore of two secondary files, A and B, using WITH NORECOVERY. 接下來,此範例會以 NORECOVERY 套用這兩個記錄備份,然後再使用 WITH RECOVERY 套用結尾記錄備份以進行還原。Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, and this is restored using WITH RECOVERY.

注意

下列範例還原順序是透過讓檔案離線來啟動,然後建立結尾記錄備份。The following sample restore sequence starts by taking the file offline and then creates a tail-log backup.

--Take the file offline.  
ALTER DATABASE database_name MODIFY FILE SET OFFLINE;  
-- Back up the currently active transaction log.  
BACKUP LOG database_name  
   TO <tail_log_backup>  
   WITH NORECOVERY;  
GO   
-- Restore the files.  
RESTORE DATABASE database_name FILE=name   
   FROM <file_backup_of_file_A>   
   WITH NORECOVERY;  
RESTORE DATABASE database_name FILE=<name> ......  
   FROM <file_backup_of_file_B>   
   WITH NORECOVERY;  
-- Restore the log backups.  
RESTORE LOG database_name FROM <log_backup>   
   WITH NORECOVERY;  
RESTORE LOG database_name FROM <log_backup>   
   WITH NORECOVERY;  
RESTORE LOG database_name FROM <tail_log_backup>   
   WITH RECOVERY;  

範例Examples

還原檔案和檔案群組To restore files and filegroups

另請參閱See Also

備份與還原:互通性與共存性 (SQL Server) Backup and Restore: Interoperability and Coexistence (SQL Server)
差異備份 (SQL Server) Differential Backups (SQL Server)
完整檔案備份 (SQL Server) Full File Backups (SQL Server)
備份概觀 (SQL Server) Backup Overview (SQL Server)
還原和復原概觀 (SQL Server) Restore and Recovery Overview (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
完整資料庫還原 (簡單復原模式) Complete Database Restores (Simple Recovery Model)
分次還原 (SQL Server) Piecemeal Restores (SQL Server)