完整数据库还原(完整恢复模式)Complete Database Restores (Full Recovery Model)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

数据库完整还原的目的是还原整个数据库。In a complete database restore, the goal is to restore the whole database. 整个数据库在还原期间处于脱机状态。The whole database is offline for the duration of the restore. 在数据库的任何部分变为联机之前,必须将所有数据恢复到同一点,即数据库的所有部分都处于同一时间点并且不存在未提交的事务。Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.

在完整恢复模式下,还原数据备份之后,必须还原所有后续的事务日志备份,然后再恢复数据库。Under the full recovery model, after you restore your data backup or backups, you must restore all subsequent transaction log backups and then recover the database. 您可以将数据库还原到这些日志备份之一的特定 恢复点You can restore a database to a specific recovery point within one of these log backups. 恢复点可以是特定的日期和时间、标记的事务或日志序列号 (LSN)。The recovery point can be a specific date and time, a marked transaction, or a log sequence number (LSN).

还原数据库时,特别是在完整恢复模式或大容量日志恢复模式下,您应使用一个还原顺序。When restoring a database, particularly under the full recovery model or bulk-logged recovery model, you should use a single restore sequence. 还原顺序 由通过一个或多个还原阶段来移动数据的一个或多个还原操作组成。A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore.

不受信任的源Untrusted sources

不 建议附加或还原来自未知或不受信任的源的数据库。We recommend that you do not attach or restore databases from unknown or untrusted sources. 这些数据库可能包含执行非预期 Transact-SQLTransact-SQL 代码的恶意代码,或通过修改架构或物理数据库结构导致错误。These databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure. 使用来自未知或不受信任的源的数据库前,请在非生产服务器上对数据库运行 DBCC CHECKDBBefore you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a non-production server. 另外,还要检查数据库中用户编写的代码,如存储过程或其他用户定义代码。Also, examine the user-written code in the database, such as stored procedures or other user-defined code.

旧版本中的备份Backups from earlier versions

有关支持从 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).

将数据库还原到故障点Restoring a Database to the Point of Failure

期待您的反馈 :如果在本文中发现过时或不正确的内容(如步骤或代码示例),请告诉我们。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 可以单击此页底部的“反馈” 部分中的“本页” 按钮。You can click the This page button in the Feedback section at the bottom of this page. 我们通常在第二天阅读有关 SQL 的每项反馈。We read every item of feedback about SQL, typically the next day. 谢谢。Thanks.

通常,将数据库恢复到故障点分为下列基本步骤:Typically, recovering a database to the point of failure involves the following basic steps:

  1. 备份活动事务日志(称为日志尾部)。Back up the active transaction log (known as the tail of the log). 此操作将创建结尾日志备份。This creates a tail-log backup. 如果活动事务日志不可用,则该日志部分的所有事务都将丢失。If the active transaction log is unavailable, all transactions in that part of the log are lost.


    在大容量日志恢复模式下,备份任何包含大容量日志操作的日志都需要访问数据库中的所有数据文件。Under the bulk-logged recovery model, backing up any log that contains bulk-logged operations requires access to all data files in the database. 如果无法访问该数据文件,则不能备份事务日志。If the data files cannot be accessed, the transaction log cannot be backed up. 在这种情况下,您必须手动重做自最近备份日志以来所做的所有更改。In that case, you have to manually redo all changes that were made since the most recent log backup.

    有关详细信息,请参阅结尾日志备份 (SQL Server)For more information, see Tail-Log Backups (SQL Server).

  2. 还原最新完整数据库备份而不恢复数据库 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  3. 如果存在差异备份,则还原最新的差异备份而不恢复数据库 (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY)。If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).

    还原最新差异备份可减少必须还原的日志备份数。Restoring the most recent differential backup reduces the number of log backups that must be restored.

  4. 从还原备份后创建的第一个事务日志备份开始,使用 NORECOVERY 依次还原日志。Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.

  5. 恢复数据库 (RESTORE DATABASE database_name WITH RECOVERY)。Recover the database (RESTORE DATABASE database_name WITH RECOVERY). 此步骤也可以与还原上一次日志备份结合使用。Alternatively, this step can be combined with restoring the last log backup.

下图说明此还原顺序。The following illustration shows this restore sequence. 故障发生后 (1),将创建结尾日志备份 (2)。After a failure occurs (1), a tail-log backup is created (2). 接着,将数据库还原到该故障点。Next, the database is restored to the point of the failure. 这涉及到还原数据库备份、后续差异备份以及在差异备份后执行的每个日志备份,包括结尾日志备份。This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.

将数据库完全还原到故障的时间点Complete database restore to the time of a failure


计划将数据库备份还原到其它服务器实例时,请参阅 通过备份和还原来复制数据库When you restore a database backup onto a different server instance, see Copy Databases with Backup and Restore.


上图中还原顺序的基本 RESTORETransact-SQLTransact-SQL 语法如下:The basic RESTORETransact-SQLTransact-SQL syntax for the restore sequence in the preceding illustration is as follows:

  1. RESTORE DATABASE database FROM full database backup WITH NORECOVERY;RESTORE DATABASE database FROM full database backup WITH NORECOVERY;

  2. RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;


    对于其他每个日志备份,重复此还原日志步骤。Repeat this restore-log step for each additional log backup.


示例:恢复到故障点 (Transact-SQL)Example: Recovering to the Point of Failure (Transact-SQL)

以下 Transact-SQLTransact-SQL 示例显示了将数据库还原到故障点的还原顺序中的基本选项。The following Transact-SQLTransact-SQL example shows the essential options in a restore sequence that restores the database to the point of failure. 此示例将创建数据库的结尾日志备份。The example creates a tail-log backup of the database. 接下来,此示例将还原完整数据库备份和日志备份,然后还原结尾日志备份。Next, the example restores a full database backup and log backup and then restores the tail-log backup. 此示例将在最后的单独步骤中恢复数据库。The example recovers the database in a separate, final step.


此示例使用在 完整数据库备份 (SQL Server)中的“兼容性支持”部分。This example uses a database backup and log backup that is created in the "Using Database Backups Under the Full Recovery Model" section in Full Database Backups (SQL Server). 在备份数据库之前, AdventureWorks2012AdventureWorks2012 示例数据库设置为使用完整恢复模式。Before the database backup, the AdventureWorks2012AdventureWorks2012 sample database was set to use the full recovery model.

USE master;  
--Create tail-log backup.  
BACKUP LOG AdventureWorks2012   
TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'    
--Restore the full database backup (from backup set 1).  
RESTORE DATABASE AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   
  WITH FILE=1,   
--Restore the regular log backup (from backup set 2).  
RESTORE LOG AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'   
  WITH FILE=2,   
--Restore the tail-log backup (from backup set 3).  
RESTORE LOG AdventureWorks2012   
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  
  WITH FILE=3,   
--recover the database:  

将数据库还原到日志备份中的某个时间点Restoring a Database to a Point Within a Log Backup

在完整恢复模式下,完整的数据库还原通常可恢复到日志备份中的某个时间点、标记的事务或 LSN。Under the full recovery model, a complete database restore can usually be recovered to a point of time, a marked transaction, or an LSN within a log backup. 但是,在大容量日志恢复模式下,如果日志备份包含大容量更改,则不能进行时点恢复。However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible.

时点还原方案示例Sample Point-in-Time Restore Scenarios

下例假定针对一个关键任务型数据库系统,每天午夜创建一个完整数据库备份;从星期一到星期六,每小时创建一个差异数据库备份;全天每 10 分钟创建一个事务日志备份。The following example assumes a mission-critical database system for which a full database backup is created daily at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. 若要将数据库还原到星期三凌晨 5:19 的状态,To restore the database to the state is was in at 5:19 A.M. 请执行以下操作:Wednesday, do the following:

  1. 还原星期二午夜创建的完整数据库备份。Restore the full database backup that was created Tuesday at midnight.

  2. 还原星期四凌晨 5:00 创建的差异数据库Restore the differential database backup that was created at 5:00 A.M. 备份。on Wednesday.

  3. 应用星期四凌晨 5:10创建的事务日志Apply the transaction log backup that was created at 5:10 A.M. 备份。on Wednesday.

  4. 应用星期三凌晨 5:20 创建的事务日志Apply the transaction log backup that was created 5:20 A.M. 备份,指定恢复进程仅应用到凌晨 5:19 之前发生的事务。on Wednesday, specifying that the recovery process applies only to transactions that occurred before 5:19 A.M.

或者,如果需要将数据库还原到它在星期四凌晨 3:04 的状态,Alternatively, if the database needs to be restored to its state at 3:04 A.M. 而在星期四凌晨 3:00 创建的差异数据库备份已不可用,Thursday, but the differential database backup that was created at 3:00 A.M. 则执行下列操作:Thursday is unavailable, do the following:

  1. 还原在星期三午夜创建的数据库备份。Restore the database backup that was created Wednesday at midnight.

  2. 还原星期四凌晨 2:00 创建的差异数据库Restore the differential database backup that was created at 2:00 A.M. 备份。on Thursday.

  3. 应用从星期四凌晨 2:10 到 3:00 创建的所有事务Apply all the transaction log backups created from 2:10 A.M. 日志to 3:00 A.M. 备份。on Thursday.

  4. 应用星期四凌晨 3:10 创建的事务日志Apply the transaction log backup that was created at 3:10 A.M. 备份,停止凌晨 3:04 的恢复进程。on Thursday, stopping the recovery process at 3:04 A.M.


有关时间点存储的示例,请参阅 将 SQL Server 数据库还原到某个时间点(完整恢复模式)中的“兼容性支持”部分。For an example of a point-in-time restore, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

相关任务Related Tasks

还原完整数据库备份To restore a full database backup

还原差异数据库备份To restore a differential database backup

还原事务日志备份To restore a transaction log backup

使用 SQL Server 管理对象 (SMO) 还原备份To restore a backup by using SQL Server Management Objects (SMO)

将数据库还原到日志备份中的某个时间点To restore a database to a point within a log backup

另请参阅See Also

RESTORE (Transact-SQL) RESTORE (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
应用事务日志备份 (SQL Server) Apply Transaction Log Backups (SQL Server)
sp_addumpdevice (Transact-SQL) sp_addumpdevice (Transact-SQL)
完整数据库备份 (SQL Server) Full Database Backups (SQL Server)
差异备份 (SQL Server) Differential Backups (SQL Server)
备份概述 (SQL Server) Backup Overview (SQL Server)
还原和恢复概述 (SQL Server)Restore and Recovery Overview (SQL Server)