分次還原 (SQL Server)Piecemeal Restores (SQL Server)

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

本主題僅與 SQL ServerSQL Server Enterprise 版 (線上還原) 或 Standard 版 (離線還原) 中包含多個檔案或檔案群組 (若是簡單模式,僅適用唯讀檔案群組) 的資料庫有關。This topic is relevant for databases in the Enterprise edition of SQL ServerSQL Server (online restore) or Standard edition (offline restore) that contain multiple files or filegroups; and, under the simple model, only for read-only filegroups.

如需分次還原及記憶體最佳化資料表的詳細資訊,請參閱 分次還原具有記憶體最佳化資料表的資料庫For information about piecemeal restore and memory-optimized tables, see Piecemeal Restore of Databases With Memory-Optimized Tables.

「分次還原」 (Piecemeal Restore) 允許對含有多個檔案群組的資料庫分段進行還原與復原。Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. 分次還原涉及一連串的還原順序,這會從主要檔案群組開始,而在某些情況下,還會從其中一個或多個次要的檔案群組開始。Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. 分次還原會維護各項檢查,以確保最後資料庫能維持一致。Piecemeal restore maintains checks to ensure that the database will be consistent in the end. 還原順序完成後,就可以直接讓復原的檔案 (如果這些檔案有效,而且與資料庫一致) 上線。After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

分次還原適用於所有恢復模式,但它對於完整模式和大量記錄模式,可以提供比簡單模式更大的彈性。Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

每個分次還原都會從稱為「部分還原順序」(Partial-Restore Sequence) 的初始還原順序開始進行。Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. 部分還原順序至少會還原並復原主要檔案群組,而在簡單復原模式下,則是所有的讀取/寫入檔案群組。Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. 在分次還原順序期間,整個資料庫必須離線。During the piecemeal-restore sequence, the whole database must go offline. 此後,資料庫便會在線上,而還原的檔案群組也可供使用。Thereafter, the database is online and restored filegroups are available. 不過,任何未還原的檔案群組會維持離線且無法存取。However, any unrestored filegroups remain offline and are not accessible. 但是,您可以稍後再利用檔案還原來還原離線檔案群組並讓它們回到線上。Any offline filegroups, however, can be restored and brought online later by a file restore.

不論資料庫使用的復原模式為何,部分還原順序從還原完整備份及指定 PARTIAL 選項的 RESTORE DATABASE 陳述式開始。Regardless of the recovery model that is used by the database, the partial-restore sequence starts with a RESTORE DATABASE statement that restores a full backup and specifies the PARTIAL option. PARTIAL 選項永遠會開始新的分次還原,因此您只能在部分還原順序的初始陳述式中指定 PARTIAL 一次。The PARTIAL option always starts a new piecemeal restore; therefore, you must specify PARTIAL only one time in the initial statement of the partial-restore sequence. 完成部分還原順序並讓資料庫回到線上後,其餘的檔案狀態都會變成「復原暫止」,因為檔案的復原已經延後。When the partial restore sequence finishes and the database is brought online, the state of the remaining files becomes "recovery pending" because their recovery has been postponed.

其後,分次還原通常包含一或多個還原順序,這稱為「檔案群組還原順序」。Subsequently, a piecemeal restore typically includes one or more restore sequences, which are called filegroup-restore sequences. 只要您願意等,就可以等著執行特定的檔案群組還原順序。You can wait to perform a specific filegroup-restore sequence for as long as you want. 每個檔案群組還原順序都會將一或多個離線檔案群組還原並復原到與資料庫一致的點上。Each filegroup-restore sequence restores and recovers one or more offline filegroups to a point consistent with the database. 檔案群組還原順序的時間和次數取決於您的復原目標、您要還原的離線檔案群組數目,以及您在每一檔案群組還原順序當中還原的檔案群組數。The timing and number of filegroup-restore sequences depends on your recovery goal, the number of offline filegroups you want to restore, and on how many of them you restore per filegroup-restore sequence.

執行分次還原的實際需求視資料庫的復原模式而定。The exact requirements for performing a piecemeal restore depend on the recovery model of the database. 如需詳細資訊,請參閱本主題稍後的「在簡單復原模式下分次還原」與「在完整復原模式下分次還原」。For more information, see "Piecemeal Restore Under the Simple Recovery Model" and "Piecemeal Restore Under the Full Recovery Model," later in this topic.

分次還原實例Piecemeal Restore Scenarios

SQL ServerSQL Server 的所有版本都支援離線分次還原。All editions of SQL ServerSQL Server support offline piecemeal restores. 在 Enterprise 版中,分次還原可以在線上或離線進行。In the Enterprise edition, a piecemeal restore can be either online or offline. 離線與線上分次還原的含意如下:The implications of offline and online piecemeal restores are as follows:

  • 離線分次還原實例Offline piecemeal restore scenario

    在離線分次還原中,資料庫會在部分還原順序之後上線。In an offline piecemeal restore, the database is online after the partial-restore sequence. 尚未還原的檔案群組會保持離線,但是有必要時,可以先將資料庫離線,再還原這些檔案群組。Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.

  • 線上分次還原實例Online piecemeal restore scenario

    如果是在部分還原順序之後的線上分次還原中,資料庫會在線上運作,且可使用主要檔案群組和任何復原的次要檔案群組。In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. 尚未還原的檔案群組會保持離線,但資料庫持續在線上運作時可視需要予以還原。Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.

    線上分次還原可以包括延遲交易。Online piecemeal restores can involve deferred transactions. 當只有還原檔案群組的子集時,可能會延遲相依於線上檔案群組之資料庫中的交易。When only a subset of filegroups has been restored, transactions in the database that depend on online filegroups might become deferred. 這是正常狀況,因為整個資料庫必須維持一致。This is typical, because the whole database must be consistent. 如需詳細資訊,請參閱 延遲交易 (SQL Server)中無用的檔案群組。For more information, see Deferred Transactions (SQL Server).

  • SQL Server In-Memory OLTPSQL Server In-Memory OLTP 分次還原狀況piecemeal restore scenario

    如需記憶體中 OLTP 資料庫之分次還原的相關資訊,請參閱 使用記憶體最佳化資料表分次備份和還原資料庫For information on Piecemeal Restores of In-Memory OLTP databases see Piecemeal Backup and Restore of Databases With Memory-Optimized Tables.

限制Restrictions

如果部分還原順序排除任何 FILESTREAM 檔案群組,則不支援時間點還原。If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. 您可以強制還原順序,以繼續進行。You can force the restore sequence to continue. 但是,絕對無法還原 RESTORE 陳述式中省略的 FILESTREAM 檔案群組。However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. 若要強制時間點還原,請指定 CONTINUE_AFTER_ERROR 選項,連同 STOPAT、STOPATMARK 或 STOPBEFOREMARK 選項,而且您也必須在後續的 RESTORE LOG 陳述式中指定這些項目。To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. 如果您指定 CONTINUE_AFTER_ERROR,則部分還原順序會成功,而 FILESTREAM 檔案群組則會變成無法復原。If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

在簡單復原模式下分次還原Piecemeal Restore Under the Simple Recovery Model

在簡單復原模式下,分次還原順序必須從完整資料庫或部分備份開始。Under the simple recovery model, the piecemeal restore sequence must start with a full database or partial backup. 然後,如果還原備份是差異基底,請接著還原最近一次的差異備份。Then, if the restored backup is a differential base, restore the latest differential backup next.

在第一個部分還原順序期間,如果只還原讀取/寫入檔案群組的子集,則當您復原部分還原的資料庫時,任何未還原的檔案群組就會變得無用。During the first partial restore sequence, if you restore only a subset of read/write filegroups, any unrestored filegroups become defunct when you recover the partially restored database. 只有下列情況才適合省略部分還原順序中的讀取/寫入檔案群組:Omitting a read/write filegroup from the partial-restore sequence is appropriate only in the following cases:

  • 想要讓未還原的檔案群組變得無用。You intend for the unrestored filegroups to become defunct.

  • 還原順序將到達每個未還原的檔案群組都變唯讀、被捨棄或解除功能 (於部分還原順序的前一個還原期間) 的復原點。The restore sequence will arrive at a recovery point at which each unrestored filegroup has become read-only, dropped, or defunct (during a previous restore in the partial-restore sequence).

  • 完整備份是在資料庫使用簡單復原模式當時所進行的,但是復原點則是在資料庫使用完整復原模式的時候。The full backup was taken while the database was using the simple recovery model, but the recovery point is at a time when the database is using the full recovery model. 如需詳細資訊,請參閱本主題稍後的「針對已從簡單復原模式切換到完整復原模式的資料庫執行分次還原」。For more information, see "Performing a Piecemeal Restore of a Database Whose Recovery Model Has Been Switched from Simple to Full," later in this topic.

在簡單復原模式下分次還原的需求Requirements for Piecemeal Restore Under the Simple Recovery Model

在簡單復原模式下,初始階段會還原並復原主要檔案群組以及所有的讀取/寫入次要檔案群組。Under the simple recovery model, the initial stage restores and recovers the primary filegroup and all read/write secondary filegroups. 初始階段完成後,就可以直接讓復原的檔案 (如果這些檔案有效,而且與資料庫一致) 上線。After the initial stage is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

此後,可以分一個或多個其他階段來還原唯讀檔案群組。Thereafter, read-only filegroups can be restored in one or more additional stages.

只有在下列情況成立時,才能對唯讀次要檔案群組進行分次還原:Piecemeal restore is available for a read-only secondary filegroup only if the following are true:

  • 備份時唯讀。Was read-only when backed up.

  • 保持唯讀 (與主要檔案群組保持邏輯上的一致)。Has remained read-only (keeping it logically consistent with the primary filegroup).

若要執行分次還原,必須遵守下列方針:To perform a piecemeal restore, the following guidelines must be followed:

  • 分次還原簡單復原模式資料庫的完整備份組必須包含下列項目:A complete set of backups for the piecemeal restore of a simple recovery model database must contain the following:

    • 包含主要檔案群組與所有檔案群組的部分或完整資料庫備份 (這些檔案群組在進行備份時都是可讀取/寫入的)。A partial or full database backup that contains the primary filegroup and all filegroups that were read/write at the time of the backup.

    • 每個唯讀檔案的備份。A backup of each read-only file.

  • 若要唯讀檔案的備份與主要檔案群組一致,從備份次要檔案群組開始,到包含主要檔案群組的備份完成為止,次要檔案群組都必須是唯讀。For the backup of a read-only file to be consistent with the primary filegroup, the secondary filegroup must have been read-only from when it was backed up until the backup that contains the primary filegroup was completed. 如果差異檔案備份是在檔案群組成為唯讀之後才建立,您可以使用這些差異檔案備份。You can use differential file backups, if they were taken after the filegroup became read-only.

分次還原階段 (簡單復原模式)Piecemeal Restore Stages (Simple Recovery Model)

分次還原實例牽涉到下列階段:The piecemeal restore scenario involves the following stages:

  • 初始階段 (還原並復原主要檔案群組與所有讀取/寫入檔案群組)Initial stage (restore and recover the primary filegroup and all read/write filegroups)

    初始階段會執行部分還原。The initial stage performs a partial restore. 部分還原順序會還原主要檔案群組、所有讀取/寫入次要檔案群組,以及 (選擇性的) 某些唯讀檔案群組。The partial restore sequence restores the primary filegroup, all read/write secondary filegroups, and (optionally) some of the read-only filegroups. 在初始階段,整個資料庫必須離線。During the initial stage, the whole database must go offline. 在初始階段之後,資料庫會持續在線上運作,且可使用還原的檔案群組。After the initial stage, the database is online, and restored filegroups are available. 不過,所有尚未還原的唯讀檔案群組會保持離線。However, any read-only filegroups that have not yet been restored, remain offline.

    初始階段中的第一個 RESTORE 陳述式必須執行下列工作:The first RESTORE statement in the initial stage must do the following:

    • 使用包含主要檔案群組與所有檔案群組 (進行備份時都是讀取/寫入的) 的部分或完整資料庫備份。Use a partial or full database backup that contains the primary filegroup and all filegroups that were read/write at the time of the backup. 部分還原的順序,通常是從還原部分備份開始。It is common to start a partial restore sequence by restoring a partial backup.

    • 指定 PARTIAL 選項,指出分次還原的開始。Specify the PARTIAL option, which indicates the start of a piecemeal restore.

    注意

    PARTIAL 選項會執行安全性檢查,確保產生的資料庫適合用來做為實際執行的資料庫。The PARTIAL option performs safety checks that ensure that the resulting database is suited for use as a production database.

    • 如果備份是完整資料庫備份,請指定 READ_WRITE_FILEGROUPS 選項。Specify the READ_WRITE_FILEGROUPS option if the backup is a full database backup.
  • 當資料庫在線上時,您可以使用一或多個線上檔案還原來還原並復原離線唯讀檔案 (在備份時是唯讀的)。While the database is online, you can use one or more online file restores to restore and recover offline read-only files that were read-only at the time of backup. 線上檔案還原的時間視您何時需要讓資料上線而定。The timing of the online file restores depends on when you want to have the data online.

    是否必須將資料還原到檔案,取決於下列條件:Whether you must restore data to a file depends on the following:

    • 與資料庫一致的有效唯讀檔案加以復原之後,不需還原任何資料,即可直接上線。Valid read-only files that are consistent with the database can be brought online directly by recovering them without restoring any data.

    • 損毀或與資料庫不一致的檔案必須在復原之前予以還原。Files that are damaged or inconsistent with the database must be restored before they are recovered.

範例Examples

在完整復原模式下分次還原Piecemeal Restore Under the Full Recovery Model

在完整復原模式或大量記錄復原模式下,分次還原適用於任何包含多個檔案群組的資料庫,而且您可以將資料庫還原到任何一個時間點。Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time. 分次還原的還原順序的運作方式如下:The restore sequences of a piecemeal restore behave as follows:

  • 部分還原順序Partial-restore sequence

    部分還原順序會還原主要檔案群組,以及選擇性地還原某些次要檔案群組。The partial restore sequence restores the primary filegroup and, optionally, some of the secondary filegroups.

    第一個 RESTORE DATABASE 陳述式必須執行下列工作:The first RESTORE DATABASE statement must do the following:

    • 指定 PARTIAL 選項。Specify the PARTIAL option. 這會指出分次還原的開始。This indicates the start of a piecemeal restore.

    • 使用包含主要檔案群組的任何完整資料庫備份。Use any full database backup that contains the primary filegroup. 部分還原的順序,通常是從還原部分備份開始。The common practice is to start a partial restore sequence by restoring a partial backup.

    • 若要還原到特定時間點,您必須在部分還原順序中指定時間。To restore to a specific point in time, you must specify the time in the partial restore sequence. 還原順序的每個連續步驟都必須指定相同的時間點。Every successive step of the restore sequence must specify the same point in time.

  • 檔案群組還原順序會讓其他檔案群組在線上與資料庫保持一致。Filegroup-restore sequences bring additional filegroups online to a point consistent with the database.

    在 Enterprise 版中,可以在資料庫仍在線上運作時還原並復原任何離線的次要檔案群組。In the Enterprise edition, any offline secondary filegroup can be restored and recovered while the database remains online. 如果特定唯讀檔案是未受損的,且與資料庫一致,就不需要還原檔案。If a specific read-only file is undamaged and consistent with the database, the file does not have to be restored. 如需詳細資訊,請參閱復原資料庫而不還原資料 (Transact-SQL)For more information, see Recover a Database Without Restoring Data (Transact-SQL).

套用記錄備份Applying Log Backups

如果唯讀檔案群組從建立檔案備份以前已經是唯讀,就不需要將記錄備份套用到檔案群組,而且檔案還原會將它略過。If a read-only filegroup has been read-only since before the file backup was created, applying log backups to the filegroup is unnecessary and is skipped by file restore. 如果檔案群組是可讀取/寫入,就必須套用無間斷記錄備份鏈結至最後一個完整或差異還原,才能將檔案群組向前復原到目前的記錄檔。If the filegroup is read/write, an unbroken chain of log backups must be applied to the last full or differential restore to bring the filegroup forward to the current log file. 如需復原流程的詳細資訊,請參閱還原和復原概觀 (SQL Server)For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

範例Examples

針對已從簡單復原模式切換到完整復原模式的資料庫執行分次還原Performing a Piecemeal Restore of a Database Whose Recovery Model Has Been Switched from Simple to Full

從完整的部分或資料庫備份起,您就可以開始針對已經從簡單復原模式切換到完整復原模式的資料庫執行分次還原。You can perform a piecemeal restore of a database that has been switched from the simple recovery model to the full recovery model since the full partial or database backup. 例如,考慮有個資料庫採取了下列步驟:For example, consider a database for which you take the following steps:

  1. 建立簡單模式資料庫的部分備份 (backup_1)。Create a partial backup (backup_1) of a simple-model database.

  2. 過一段時間以後,將復原模式變更為「完整」。After some time, change the recovery model to full.

  3. 建立差異備份。Create a differential backup.

  4. 開始進行記錄備份。Start taking log backups.

之後,以下的順序有效:Thereafter, the following sequence is valid:

  1. 忽略部分次要檔案群組的部分還原。A partial restore that omits some secondary filegroups.

  2. 差異還原後有其他必要的還原。A differential restore followed by any other needed restores.

  3. 稍後,從 backup_1 部分備份進行讀取/寫入次要檔案群組 WITH NORECOVERY 檔案還原Later, a file restore of a read/write secondary filegroup WITH NORECOVERY from the backup_1 partial backup

  4. 差異備份之後緊接著原始分次還原順序中所還原的任何其他備份,可還原資料直到原始的復原點為止。The differential backup followed by any other backups that were restored in the original piecemeal restore sequence to restore the data up to the original recovery point.

另請參閱See Also

套用交易記錄備份 (SQL Server) Apply Transaction Log Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
將 SQL Server 資料庫還原至某個時間點 (完整復原模式) Restore a SQL Server Database to a Point in Time (Full Recovery Model)
還原和復原概觀 (SQL Server) Restore and Recovery Overview (SQL Server)
規劃和執行還原順序 (完整復原模式) Plan and Perform Restore Sequences (Full Recovery Model)
還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)