檔案還原 (簡單復原模式)File Restores (Simple 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 simple-model databases that contain at least one read-only secondary filegroup.

檔案還原的目的是還原一個或多個損毀的檔案,而不還原整個資料庫。In a file restore, the goal is to restore one or more damaged files without restoring the whole database. 在簡單復原模式下,僅支援唯讀檔案的檔案備份。Under the simple recovery model, file backups are supported only for read-only files. 透過還原資料庫或部分備份,永遠一併還原主要檔案群組和讀取/寫入次要檔案群組。The primary filegroup and read/write secondary filegroups are always restored together, by restoring a database or partial backup.

這些檔案還原實例如下: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.

    如需線上頁面和檔案還原支援的資訊,請參閱 Database Engine 功能及工作For information about support for online page and file restore, see Database Engine Features and Tasks. 如需線上還原的詳細資訊,請參閱線上還原 (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.

    本主題內容:In this Topic:

  • 簡單復原模式下的檔案和檔案群組還原概觀Overview of File and Filegroup Restore Under the Simple Recovery Model

  • 相關工作Related Tasks

簡單復原模式下的檔案和檔案群組還原概觀 Overview of File and Filegroup Restore Under the Simple Recovery Model

檔案還原案例是單一還原順序,涵蓋複製、向前復原及復原適當資料等動作,如下所示:A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data as follows:

  1. 從最新的檔案備份來還原每一個損毀的檔案。Restore each damaged file from its most recent file backup.

  2. 針對每個已還原的檔案,還原其最新的差異檔案備份,並復原資料庫。Restore the most recent differential file backup for each restored file and recover the database.

檔案還原順序的 Transact-SQL 步驟 (簡單復原模式)Transact-SQL Steps for File Restore Sequence (Simple Recovery Model)

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

還原順序只包含兩個 Transact-SQLTransact-SQL 陳述式。The restore sequence contains only two Transact-SQLTransact-SQL statements. 第一個陳述式會還原次要檔案 (即 A檔案),而此檔案是使用 WITH NORECOVERY 進行還原。The first statement restores a secondary file, file A, which is restored using WITH NORECOVERY. 第二項作業還原其他兩個檔案 ( BC ),而這些檔案是使用 WITH RECOVERY 從不同的備份裝置進行還原。The second operation restores two other files, B and C which are restored using WITH RECOVERY from a different backup device:

  1. RESTORE DATABASE database FILE =name_of_file_ARESTORE DATABASE database FILE =name_of_file_A

    FROM file_backup_of_file_AFROM file_backup_of_file_A

    WITH NORECOVERY;WITH NORECOVERY;

  2. RESTORE DATABASE database FILE =name_of_file_B,name_of_file_CRESTORE DATABASE database FILE =name_of_file_B,name_of_file_C

    FROM file_backup_of_files_B_and_CFROM file_backup_of_files_B_and_C

    WITH RECOVERY;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)