還原和復原概觀 (SQL Server)Restore and Recovery Overview (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 資料庫,資料庫管理員必須依邏輯正確和有意義的還原順序來還原一組 SQL ServerSQL Server 備份。To recover a SQL ServerSQL Server database from a failure, a database administrator has to restore a set of SQL ServerSQL Server backups in a logically correct and meaningful restore sequence. SQL ServerSQL Server 還原及復原,可從一整個資料庫、單一資料檔或資料頁面的備份還原資料,如下所示:restore and recovery supports restoring data from backups of a whole database, a data file, or a data page, as follows:

  • 資料庫 ( 「完整資料庫還原」(Complete database restore))The database (a complete database restore)

    將會還原並復原整個資料庫,且在還原與復原作業期間,資料庫會離線。The whole database is restored and recovered, and the database is offline for the duration of the restore and recovery operations.

  • 資料檔 ( 「檔案還原」)The data file (a file restore)

    還原與復原一個資料檔或一組檔案。A data file or a set of files is restored and recovered. 在檔案還原過程中,包含該檔案的檔案群組會在還原的持續時間內自動離線。During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore. 任何存取離線檔案群組的嘗試都會產生錯誤。Any attempt to access an offline filegroup causes an error.

  • 資料頁 ( 「分頁還原」(Page restore))The data page (a page restore)

    在完整復原模式或大量記錄復原模式下,您可以還原各個資料庫。Under the full recovery model or bulk-logged recovery model, you can restore individual databases. 不論檔案群組的數目為何,在任何資料庫上都可以執行分頁還原。Page restores can be performed on any database, regardless of the number of filegroups.

SQL ServerSQL Server 備份與還原可以跨所有支援的作業系統運作。backup and restore work across all supported operating systems. 如需支援的作業系統詳細資訊,請參閱 安裝 SQL Server 2016 的硬體與軟體需求For information about the supported operating systems, see Hardware and Software Requirements for Installing SQL Server 2016. 如需舊版 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).

還原案例概觀Overview of Restore Scenarios

中的 「還原案例」 SQL ServerSQL Server (Restore scenario) 是指先從一個或多個備份還原資料,再復原資料庫的程序。A restore scenario in SQL ServerSQL Server is the process of restoring data from one or more backups and then recovering the database. 支援的還原實例視資料庫的復原模式與 SQL ServerSQL Server的版本而定。The supported restore scenarios depend on the recovery model of the database and the edition of SQL ServerSQL Server.

下表介紹各種復原模式可能支援的還原實例。The following table introduces the possible restore scenarios that are supported for different recovery models.

中的Restore scenario 在簡單復原模式下Under simple recovery model 在完整/大量記錄復原模式下Under full/bulk-logged recovery models
完整資料庫還原Complete database restore 這是基本還原策略。This is the basic restore strategy. 完整資料庫還原可能只包括還原和復原完整資料庫備份。A complete database restore might involve simply restoring and recovering a full database backup. 此外,完整資料庫還原也可能包括還原完整資料庫備份,接著再還原和復原差異備份。Alternatively, a complete database restore might involve restoring a full database backup followed by restoring and recovering a differential backup.

如需詳細資訊,請參閱完整資料庫還原 (簡單復原模式)For more information, see Complete Database Restores (Simple Recovery Model).
這是基本還原策略。This is the basic restore strategy. 完整資料庫還原包括還原完整資料庫備份和選用的差異備份 (如果有的話),然後依照順序還原所有後續的記錄備份。A complete database restore involves restoring a full database backup and, optionally, a differential backup (if any), followed by restoring all subsequent log backups (in sequence). 復原最後一個記錄備份,並且加以還原 (RESTORE WITH RECOVERY),即完成完整資料庫還原。The complete database restore is finished by recovering the last log backup and also restoring it (RESTORE WITH RECOVERY).

如需詳細資訊,請參閱完整資料庫還原 (完整復原模式)For more information, see Complete Database Restores (Full Recovery Model)
File restore *File restore * 還原一個或多個損毀的唯讀檔案,而不還原整個資料庫。Restore one or more damaged read-only files, without restoring the entire database. 唯有當資料庫至少有一個唯讀檔案群組時,才能使用檔案還原。File restore is available only if the database has at least one read-only filegroup. 還原一個或多個檔案,而不還原整個資料庫。Restores one or more files, without restoring the entire database. 可以在資料庫離線時,或在資料庫仍在線上時 (適用於某些 SQL ServerSQL Server版本) 執行檔案還原。File restore can be performed while the database is offline or, for some editions of SQL ServerSQL Server, while the database remains online. 在檔案還原期間,包含正在還原中之檔案的檔案群組一律為離線狀態。During a file restore, the filegroups that contain the files that are being restored are always offline.
分頁還原Page restore 不適用Not applicable 還原一個或多個損毀的頁面。Restores one or more damaged pages. 可以在資料庫離線時,或在資料庫仍在線上時 (適用於某些 SQL ServerSQL Server版本) 執行頁面還原。Page restore can be performed while the database is offline or, for some editions of SQL ServerSQL Server, while the database remains online. 在分頁還原期間,正在還原中的頁面一律為離線狀態。During a page restore, the pages that are being restored are always offline.

未中斷的記錄備份鏈結必須可用 (直到目前記錄檔),且必須全部套用,才能使分頁與目前記錄檔保持一樣新。An unbroken chain of log backups must be available, up to the current log file, and they must all be applied to bring the page up-to-date with the current log file.

如需詳細資訊,請參閱還原頁面 (SQL Server)For more information, see Restore Pages (SQL Server).
分次還原 *Piecemeal restore * 在檔案群組層級上,從主要檔案群組開始,接著是所有讀取/寫入次要檔案群組,分階段還原和復原資料庫。Restore and recover the database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups. 從主要檔案群組開始,在檔案群組層級上分階段還原和復原資料庫。Restore and recover the database in stages at the filegroup level, starting with the primary filegroup.

如需詳細資訊,請參閱分次還原 (SQL Server)For more information, see Piecemeal Restores (SQL Server)

* 只有 Enterprise 版才支援線上還原。* Online restore is supported only in the Enterprise edition.

還原資料庫的步驟Steps to restore a database

若要執行檔案還原,Database EngineDatabase Engine 會執行兩個步驟:To perform a file restore, the Database EngineDatabase Engine executes two steps:

  • 建立任何遺失的資料庫檔案。Creates any missing database file(s).

  • 將資料從備份裝置複製到資料庫檔案。Copies the data from the backup devices to the database file(s).

若要執行資料庫還原,Database EngineDatabase Engine 會執行三個步驟:To perform a database restore, the Database EngineDatabase Engine executes three steps:

  • 建立資料庫和交易記錄檔 (若尚未存在的話)。Creates the database and transaction log files if they do not already exist.

  • 從資料庫的備份媒體將所有資料、記錄和索引頁複製到資料庫檔案。Copies all the data, log, and index pages from the backup media of a database to the database files.

  • 在所謂的復原流程中套用交易記錄。Applies the transaction log in what is known as the recovery process.

不論還原資料的方式如何,在復原資料庫之前, SQL Server Database EngineSQL Server Database Engine 保證整個資料庫在邏輯上是一致的。Regardless of how data is restored, before a database can be recovered, the SQL Server Database EngineSQL Server Database Engine guarantees that the whole database is logically consistent. 例如,如果您要還原檔案,除非您已經將其向前復原到足以和資料庫保持一致的程度,否則無法加以復原並使其回到線上。For example, if you restore a file, you cannot recover it and bring it online until it has been rolled far enough forward to be consistent with the database.

檔案或分頁還原的優點Advantages of a File or Page restore

還原與復原檔案或頁面 (而非整個資料庫) 可提供下列優點:Restoring and recovering files or pages, instead of the whole database, provides the following advantages:

  • 還原較少的資料,可縮短複製和復原資料所需的時間。Restoring less data reduces the time required to copy and recover it.

  • SQL ServerSQL Server 上,還原檔案或頁面可以讓資料庫中的其他資料在還原作業期間維持線上狀態。On SQL ServerSQL Server restoring files or pages might allow other data in the database to remain online during the restore operation.

復原和交易記錄Recovery and the transaction log

針對大多數的還原案例,必須套用交易記錄備份並允許 SQL Server Database EngineSQL Server Database Engine 執行復原流程,資料庫才能上線。For most restore scenarios, it is necessary to apply a transaction log backup and allow the SQL Server Database EngineSQL Server Database Engine to run the recovery process for the database to be brought online. 復原是 SQL ServerSQL Server 使用的的流程,為的是讓每個資料庫都能以交易一致 (或正常) 狀態啟動。Recovery is the process used by SQL ServerSQL Server for each database to start in a transactionally consistent - or clean - state.

在容錯移轉或其他不正常關機的情況下,資料庫可能會停留在緩衝區快取中有些修改尚未寫入資料檔,且未完成交易已在資料檔中作了一些修改的狀態。In case of a failover or other non-clean shut down, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. 啟動 SQL ServerSQL Server 的執行個體時,其會執行每個資料庫的復原;這些復原都是以最後一個資料庫檢查點為基礎的三個階段所組成:When an instance of SQL ServerSQL Server is started, it runs a recovery of each database, which consists of three phases, based on the last database checkpoint:

  • 分析階段會分析交易記錄來判斷最後一個檢查點為何,並建立中途分頁資料表 (Dirty Page Table,DPT) 和使用中交易資料表 (Active Transaction Table,ATT)。Analysis Phase analyzes the transaction log to determine what is the last checkpoint, and creates the Dirty Page Table (DPT) and the Active Transaction Table (ATT). DPT 包含資料庫關機時已變更的分頁記錄。The DPT contains records of pages that were dirty at the time the database was shut down. ATT 包含資料庫不正常關機時仍在使用中的交易記錄。The ATT contains records of transactions that were active at the time the database was not cleanly shut down.

  • 重做階段會向前復原資料庫關機時,記錄中每個已記錄且可能尚未寫入資料檔的修改內容。Redo Phase rolls forwards every modification recorded in the log that may not have been written to the data files at the time the database was shut down. 其會在 DPT 中找到成功進行全資料庫復原時所需要的最小記錄序號 (minLSN),並標記所有中途分頁上所需重做作業的開始。The minimum log sequence number (minLSN) required for a successful database-wide recovery is found in the DPT, and marks the start of the redo operations needed on all dirty pages. 在此階段,SQL Server Database EngineSQL Server Database Engine 會將所有屬於已認可交易的中途分頁寫入磁碟。At this phase, the SQL Server Database EngineSQL Server Database Engine writes to disk all dirty pages belonging to committed transactions.

  • 復原階段會復原 ATT 中找到的未完成交易,確保資料庫的完整性。Undo Phase rolls back incomplete transactions found in the ATT to make sure the integrity of the database is preserved. 回復之後,資料庫會上線,而且不再有交易記錄備份可以套用到資料庫。After rollback, the database goes online, and no more transaction log backups can be applied to the database.

每個資料庫復原階段進度的相關資訊都會記錄在SQL ServerSQL Server錯誤記錄檔中。Information about the progress of each database recovery stage is logged in the SQL ServerSQL Server error log. 資料庫復原進度也可以使用擴充事件進行追蹤。The database recovery progress can also be tracked using Extended Events. 如需詳細資訊,請參閱部落格文章資料庫復原進度的新擴充事件For more information, see the blog post New extended events for database recovery progress.

注意

針對分次還原案例,如果唯讀檔案群組從建立檔案備份以前已經是唯讀,就不需要將記錄備份套用到檔案群組,且檔案還原會跳過它。For a Piecemeal restore scenario, 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.

注意

為了最大化企業環境中資料庫的可用性,SQL ServerSQL Server Enterprise Edition 可以在重做階段後,且「復原階段」仍在執行時就讓資料庫上線。To maximize the availability of databases in an enterpirse environment, SQL ServerSQL Server Enterprise Edition can bring a database online after the Redo Phase, while the Undo Phase is still executing. 這稱為「快速復原」。This is known as Fast Recovery.

復原模式和支援的還原作業Recovery models and supported restore operations

資料庫可用的還原作業,取決於其復原模式。The restore operations that are available for a database depend on its recovery model. 下表摘要說明每一種復原模式是否支援給定的還原實例,及其支援的範圍。The following table summarizes whether and to what extent each of the recovery models supports a given restore scenario.

還原作業Restore operation 完整復原模式Full recovery model 大量記錄復原模式Bulk-logged recovery model 簡單復原模式Simple recovery model
資料復原Data recovery 完整復原 (如果有記錄可以使用)。Complete recovery (if the log is available). 有損失部分資料的風險。Some data-loss exposure. 自上次完整或差異備份之後的任何資料,都會遺失。Any data since last full or differential backup is lost.
時間點還原Point-in-time restore 記錄備份涵蓋的任何時間。Any time covered by the log backups. 如果記錄備份含有大量記錄變更,則不允許。Disallowed if the log backup contains any bulk-logged changes. 不支援。Not supported.
File restore *File restore * 完整支援。Full support. 有時。**Sometimes.** 僅適用於唯讀的次要檔案。Available only for read-only secondary files.
Page restore *Page restore * 完整支援。Full support. 有時。**Sometimes.** 無。None.
分次 (檔案群組-等級) 還原 *Piecemeal (filegroup-level) restore * 完整支援。Full support. 有時。**Sometimes.** 僅適用於唯讀的次要檔案。Available only for read-only secondary files.

* 只有 SQL ServerSQL Server* Available only in the Enterprise edition of SQL ServerSQL Server

** 如需了解必要條件,請參閱此主題稍後的 簡單復原模式下的還原限制** For the required conditions, see Restore Restrictions Under the Simple Recovery Model, later in this topic.

重要

不論資料庫的復原模式為何,SQL ServerSQL Server 備份都無法還原至比建立備份版本還舊的 SQL Server Database EngineSQL Server Database Engine 版本。Regardless of the recovery model of a database, a SQL ServerSQL Server backup cannot be restored to a SQL Server Database EngineSQL Server Database Engine version that is older than the version that created the backup.

簡單復原模式下的還原案例Restore scenarios under the Simple Recovery Model

簡單復原模式在還原作業上具有下列限制:The simple recovery model imposes the following restrictions on restore operations:

如果這些限制中有任何一項不適合您的復原需要,即建議您考慮使用完整復原模式。If any of these restrictions are inappropriate for your recovery needs, we recommend that you consider using the full recovery model. 如需詳細資訊,請參閱 備份概觀 (SQL Server)For more information, see Backup Overview (SQL Server).

重要

不論資料庫的復原模式為何,比建立備份之版本還舊的 SQL ServerSQL Server 版本,都無法還原 SQL ServerSQL Server 備份。Regardless of the recovery model of a database, a SQL ServerSQL Server backup cannot be restored by a version of SQL ServerSQL Server that is older than the version that created the backup.

大量記錄復原模式下的還原Restore Under the Bulk-Logged Recovery Model

本節討論大量記錄復原模式的特殊還原考量,此為專門用做完整復原模式的補充。This section discusses restore considerations that are unique to bulk-logged recovery model, which is intended exclusively as a supplement to the full recovery model.

注意

如需大量記錄復原模式的介紹,請參閱交易記錄 (SQL Server)For an introduction to the bulk-logged recovery model, see The Transaction Log (SQL Server).

一般而言,大量記錄復原模式與完整復原模式類似,針對完整復原模式所描述的資訊也同時適用於兩者。Generally, the bulk-logged recovery model is similar to the full recovery model, and the information described for the full recovery model also applies to both. 但是,大量記錄復原模式會影響時間點復原與線上還原。However, point-in-time recovery and online restore are affected by the bulk-logged recovery model.

時間點復原的限制Restrictions for Point-in-time Recovery

如果大量記錄復原模式下建立的記錄備份包含大量記錄變更,就不允許時間點復原。If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. 嘗試對包含大量變更的記錄備份執行時間點復原將會造成還原作業失敗。Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

對線上還原的限制Restrictions for Online Restore

線上還原順序只有在符合下列條件時才能運作:An online restore sequence works only if the following conditions are met:

  • 必須在開始還原順序之前完成所有必要的記錄備份。All required log backups must have been taken before the restore sequence starts.

  • 必須在開始線上還原順序之前先備份大量變更。Bulk changes must be backed before starting the online restore sequence.

  • 如果資料庫中存在大量變更,則所有的檔案都必須在線上,或是無用If bulk changes exist in the database, all files must be either online or defunct. (這表示它不再是資料庫的一部分)。(This means that it is no longer part of the database.)

如果沒有符合這些條件,線上還原順序就會失敗。If these conditions are not met, the online restore sequence fails.

注意

我們建議您先切換到完整復原模式,再開始線上還原。We recommend switching to the full recovery model before starting an online restore. 如需詳細資訊,請參閱復原模式 (SQL Server )For more information, see Recovery Models (SQL Server).

如需有關如何執行線上還原的詳細資訊,請參閱線上還原 (SQL Server)For information about how to perform an online restore, see Online Restore (SQL Server).

Database Recovery Advisor (SQL Server Management Studio)Database Recovery Advisor (SQL Server Management Studio)

Database Recovery Advisor 有助於建構實作最佳化正確還原順序的還原計畫。The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences. 我們已經處理了客戶所要求的許多已知資料庫還原問題和增強功能。Many known database restore issues and enhancements requested by customers have been addressed. Database Recovery Advisor 導入的主要增強功能包括:Major enhancements introduced by the Database Recovery Advisor include the following:

  • 還原計畫演算法: 用來建構還原計畫的演算法已經大幅改善,特別是針對複雜的還原狀況。Restore-plan algorithm: The algorithm used to construct restore plans has improved significantly, particularly for complex restore scenarios. 相較於舊版 SQL ServerSQL Server而言,可更有效率地處理許多邊緣案例 (包括時間點還原的分岔案例)。Many edge cases, including forking scenarios in point-in-time restores, are handled more efficiently than in previous versions of SQL ServerSQL Server.

  • 時間點還原: Database Recovery Advisor 大幅簡化了將資料庫還原到特定時間點的作業。Point-in-time restores: The Database Recovery Advisor greatly simplifies restoring a database to a given point in time. 視覺備份時間表大幅增強時間點還原的支援。A visual backup timeline significantly enhances support for point-in-time restores. 這個視覺化時間表可讓您識別當做還原資料庫之目標復原點的可行時間點。This visual timeline allows you to identify a feasible point in time as the target recovery point for restoring a database. 時間表可加快周遊分岔復原路徑 (跨多個復原分岔之路徑)。The timeline facilitates traversing a forked recovery path (a path that spans recovery forks). 特定時間點還原計畫會自動包含與還原至目標時間點 (日期和時間) 有關的備份。A given point-in-time restore plan automatically includes the backups that are relevant to the restoring to your target point in time (date and time). 如需詳細資訊,請參閱將 SQL Server 資料庫還原至某個時間點 (完整復原模式)For more information, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

如需有關 Database Recovery Advisor 的詳細資訊,請參閱下列 SQL ServerSQL Server 管理能力部落格:For more information, see about the Database Recovery Advisor, see the following SQL ServerSQL Server Manageability blogs:

加速資料庫復原Accelerated database recovery

加速資料庫復原可在 SQL Server 2019 (15.x)SQL Server 2019 (15.x)Azure SQL DatabaseAzure SQL Database 中使用。Accelerated database recovery is available in SQL Server 2019 (15.x)SQL Server 2019 (15.x) and Azure SQL DatabaseAzure SQL Database. 加速資料庫復原藉由重新設計SQL Server Database EngineSQL Server Database Engine復原流程來大幅改善資料庫可用性,尤其是針對長時間執行的交易。Accelerated database recovery greatly improves database availability, especially in the presence of long-running transactions, by redesigning the SQL Server Database EngineSQL Server Database Engine recovery process. 啟用加速資料庫復原的資料庫,其在容錯移轉或其他非正常關機之後完成復原流程的速度會大幅加快。A database for which accelerated database recovery was enabled completes the recovery process significantly faster after a failover or other non-clean shut down. 啟用時,加速資料庫復原也會大幅加快完成復原已取消長時間執行交易的速度。When enabled, Accelerated database recovery also completes rollback of canceled long-running transactions significantly faster.

您可以使用下列語法,為 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 上的每個資料庫上啟用加速資料庫復原:You can enable accelerated database recovery per-database on SQL Server 2019 (15.x)SQL Server 2019 (15.x) using the following syntax:

ALTER DATABASE <db_name> SET ACCELERATED_DATABASE_RECOVERY = ON;

注意

Azure SQL DatabaseAzure SQL Database 上,加速資料庫復原預設為啟用。Accelerated database recovery is enabled by default on Azure SQL DatabaseAzure SQL Database.

另請參閱See Also

備份概觀 (SQL Server) Backup Overview (SQL Server)
交易記錄 (SQL Server) The Transaction Log (SQL Server)
SQL Server 交易記錄架構與管理指南 SQL Server Transaction Log Architecture and Management Guide
SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
套用交易記錄備份 (SQL Server)Apply Transaction Log Backups (SQL Server)