还原与恢复概述 (SQL Server)Restore and Recovery Overview (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否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:

  • 数据库(“数据库完整还原” )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.

  • 数据页(“页面还原” )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 ”是从一个或多个备份还原数据、继而恢复数据库的过程。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 * 还原损坏的只读文件,但不还原整个数据库。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 Edition 中支持联机还原。* Online restore is supported only in the Enterprise edition.

还原数据库的步骤Steps to restore a database

若要执行文件还原,数据库引擎Database Engine 执行两个步骤:To perform a file restore, the 数据库引擎Database Engine executes two steps:

  • 创建所有丢失的数据库文件。Creates any missing database file(s).

  • 将数据从备份设备复制到数据库文件。Copies the data from the backup devices to the database file(s).

若要执行数据库还原,数据库引擎Database Engine 执行三个步骤:To perform a database restore, the 数据库引擎Database 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 数据库引擎SQL Server Database Engine 都会保证整个数据库在逻辑上的一致性。Regardless of how data is restored, before a database can be recovered, the SQL Server 数据库引擎SQL 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 数据库引擎SQL Server Database Engine 运行恢复过程 才能使数据库联机。For most restore scenarios, it is necessary to apply a transaction log backup and allow the SQL Server 数据库引擎SQL 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:

  • “分析阶段” 分析事务日志以确定最后一个检查点,并创建脏页表 (DPT) 和活动事务表 (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. 成功进行数据库范围内恢复所需的最小日志序列号 (minLSN) 在 DPT 中找到,并标记了所有脏页上所需的重做操作的开始时间。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 数据库引擎SQL Server Database Engine 会将属于提交的事务的所有脏页写入磁盘。At this phase, the SQL Server 数据库引擎SQL 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 * 完全支持。Full support. 不完全支持。 **Sometimes.** 仅对只读辅助文件可用。Available only for read-only secondary files.
页面还原 *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 数据库引擎SQL Server Database Engine 版本。Regardless of the recovery model of a database, a SQL ServerSQL Server backup cannot be restored to a SQL Server 数据库引擎SQL 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).

数据库恢复顾问 (SQL Server Management Studio)Database Recovery Advisor (SQL Server Management Studio)

数据库恢复顾问简化了制定还原计划的过程,可以很轻松地实现最优的正确还原顺序。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. 数据库恢复顾问引入的主要增强功能包括: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.

  • 时间点还原: 数据库恢复顾问极大地简化了将数据库还原到给定时间点的过程。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).

有关详细信息,请参阅下列 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 数据库Azure SQL Database 中提供了加速数据库恢复Accelerated database recovery is available in SQL Server 2019 (15.x)SQL Server 2019 (15.x) and Azure SQL 数据库Azure SQL Database. 通过重新设计 SQL Server 数据库引擎SQL Server Database Engine 恢复过程,加速数据库恢复极大地提高了数据库可用性,尤其是存在长时间运行的事务时。Accelerated database recovery greatly improves database availability, especially in the presence of long-running transactions, by redesigning the SQL Server 数据库引擎SQL 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 数据库Azure SQL Database 上将默认启用加速数据库恢复。Accelerated database recovery is enabled by default on Azure SQL 数据库Azure 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)