還原和復原概觀 (SQL Server)Restore and Recovery Overview (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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).

本主題內容:In this Topic:

還原案例概觀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 involve 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.

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

不論還原資料的方式如何,在復原資料庫之前, 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 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 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 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 ordefunct. (這表示它不再是資料庫的一部分)。(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 Preview CTP 2.3 引進適用於內部部署 SQL Server 的加速資料庫復原SQL Server 2019 preview CTP 2.3 introduces Accelerated database recovery for SQL Server on-premises. 加速資料庫復原藉由重新設計 SQL Server 的資料庫引擎復原處理序來大幅提升資料庫可用性,尤其是針對長時間執行的交易。Accelerated database recovery greatly improves database availability, especially in the presence of long running transactions, by redesigning the SQL Server database engine recovery process. 資料庫復原是一項 SQL Server 為了讓每個資料庫都能以交易一致 (或正常) 狀態啟動所使用的處理序。Database recovery is the process SQL Server uses for each database to start at a transactionally consistent - or clean - state. 啟用加速資料庫復原的資料庫,其在容錯移轉或其他非正常關機之後完成復原的速度會大幅加快。A database, with accelerated database recovery enabled, completes recovery significantly faster after a fail over or other non-clean shutdown.

您可以可以使用下列語法,為 SQL Server 2019SQL Server 2019 CTP 2.3 或更新版本上的每個資料庫啟用加速資料庫復原:You can enable accelerated database recovery per-database on SQL Server 2019SQL Server 2019 CTP 2.3 or later using the following syntax:

ALTER DATABASE <db_name> SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF}

注意

無須在 Azure SQL DB 中使用此語法也能利用這項功能,因為它預設為開啟。This syntax is not required to take advantage of this feature in Azure SQL DB, where it is on by default.

若您有可能進行大型交易的重要資料庫,請在預覽期間實驗這項功能。If you have critical databases that are prone to large transactions, experiment with this feature during the preview. 請將意見反應提供給 SQL ServerSQL Server小組Provide feedback to SQL ServerSQL Server team.

相關內容Related Content

無。None.

另請參閱See Also

備份概觀 (SQL Server)Backup Overview (SQL Server)