将数据库还原到故障点 - 完整恢复Restore Database to Point of Failure - Full Recovery

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何还原到故障点。This topic explains how to restore to the point of failure. 本主题仅与那些使用完整或大容量日志恢复模式的数据库相关。The topic is relevant only for databases that are using the full or bulk-logged recovery models.

还原到故障点To restore to the point of failure

  1. 通过运行以下基本 BACKUP 语句来备份日志尾部:Back up the tail of the log by running the following basic BACKUP statement:

    BACKUP LOG <database_name> TO <backup_device>   
       WITH NORECOVERY, NO_TRUNCATE;  
    
  2. 通过运行以下基本 RESTORE DATABASE 语句来还原完整数据库备份:Restore a full database backup by running the following basic RESTORE DATABASE statement:

    RESTORE DATABASE <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  3. 或者,通过运行以下基本 RESTORE DATABASE 语句来还原差异数据库备份:Optionally, restore a differential database backup by running the following basic RESTORE DATABASE statement:

    RESTORE DATABASE <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  4. 通过在 RESTORE LOG 语句中指定 WITH NORECOVERY 以应用每个事务日志(包括步骤 1 中创建的结尾日志备份):Apply each transaction log, including the tail-log backup you created in step 1, by specifying WITH NORECOVERY in the RESTORE LOG statement:

    RESTORE LOG <database_name> FROM <backup_device>   
       WITH NORECOVERY;  
    
  5. 通过运行以下 RESTORE DATABASE 语句来恢复数据库:Recover the database by running the following RESTORE DATABASE statement:

    RESTORE DATABASE <database_name>   
       WITH RECOVERY;  
    

示例Example

必须先完成下列准备工作,才能运行此示例:Before you can run the example, you must complete the following preparations:

  1. AdventureWorks2012AdventureWorks2012 数据库的默认恢复模式是简单恢复模式。The default recovery model of the AdventureWorks2012AdventureWorks2012 database is the simple recovery model. 由于该恢复模式不支持还原到故障点,因此请将 AdventureWorks2012AdventureWorks2012 设置为使用完整恢复模式,方法是运行以下 ALTER DATABASE 语句:Because this recovery model does not support restoring to the point of a failure, set AdventureWorks2012AdventureWorks2012 to use the full recovery model by running the following ALTER DATABASE statement:

    USE master;  
    GO  
    ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  
    
  2. 通过使用以下 BACKUP 语句,创建数据库的完整数据库备份:Create a full database back of the database by using the following BACKUP statement:

    BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Data.bck';  
    
  3. 创建例程日志备份:Create a routine log backup:

    BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck';  
    

以下示例在创建 AdventureWorks2012AdventureWorks2012 数据库的结尾日志备份后,将还原先前创建的备份。The following example restores the backups that are created previously, after creating a tail-log backup of the AdventureWorks2012AdventureWorks2012 database. (此步骤假设可以访问日志磁盘。)(This step assumes that the log disk can be accessed.)

首先,该示例将创建捕获活动日志的数据库结尾日志备份,并使数据库处于还原状态。First, the example creates a tail-log backup of the database that captures the active log and leaves the database in the Restoring state. 然后,该示例将还原数据库备份,应用先前创建的例程日志备份,并应用结尾日志备份。Then, the example restores the database backup, applies the routine log backup created previously, and applies the tail-log backup. 最后,该示例将在单独的步骤中恢复数据库。Finally, the example recovers the database in a separate step.

备注

默认行为是将数据库恢复作为还原最终备份语句的一部分。The default behavior is to recover a database as part of the statement that restores the final backup.

/* Example of restoring a to the point of failure */  
-- Step 1: Create a tail-log backup by using WITH NORECOVERY.  
BACKUP LOG AdventureWorks2012  
   TO DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 2: Restore the full database backup.  
RESTORE DATABASE AdventureWorks2012  
   FROM DISK = 'C:\AdventureWorks2012_Data.bck'  
   WITH NORECOVERY;  
GO  
-- Step 3: Restore the first transaction log backup.  
RESTORE LOG AdventureWorks2012  
   FROM DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 4: Restore the tail-log backup.  
RESTORE LOG AdventureWorks2012  
   FROM  DISK = 'C:\AdventureWorks2012_Log.bck'  
   WITH NORECOVERY;  
GO  
-- Step 5: Recover the database.  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

另请参阅See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)