通过

  • 项目

如何还原差异数据库备份 (Transact-SQL)

本主题介绍了如何还原差异数据库备份。

还原差异数据库备份

  1. 执行 RESTORE DATABASE 语句并指定 NORECOVERY 子句，以还原在差异数据库备份之前执行的完整数据库备份。有关详细信息，请参阅如何还原完整备份。

  2. 执行 RESTORE DATABASE 语句以还原差异数据库备份，同时指定：

    • 要应用差异数据库备份的数据库的名称。

    • 从其中还原差异数据库备份的备份设备。

    • NORECOVERY 子句，前提是在还原差异数据库备份之后，还要应用事务日志备份。否则应指定 RECOVERY 子句。

  3. 通过完整恢复模式或大容量日志恢复模式，还原差异数据库备份可将数据库还原到差异数据库备份完成的点。若要恢复到故障点，在创建完最后一个差异数据库备份之后，必须应用所有已创建的事务日志备份。有关详细信息，请参阅如何应用事务日志备份 (Transact-SQL)。

示例

A. 还原数据库和差异数据库备份

以下示例将还原 MyAdvWorks 数据库及其差异数据库备份。

-- Assume the database is lost, and restore full database, 
-- specifying the original full database backup and NORECOVERY, 
-- which allows subsequent restore operations to proceed.
RESTORE DATABASE MyAdvWorks
   FROM MyAdvWorks_1
   WITH NORECOVERY
GO
-- Now restore the differential database backup, the second backup on 
-- the MyAdvWorks_1 backup device.
RESTORE DATABASE MyAdvWorks
   FROM MyAdvWorks_1
   WITH FILE = 2,
   RECOVERY
GO

B. 还原数据库、差异数据库以及事务日志备份

以下示例将还原 MyAdvWorks 数据库及其差异数据库和事务日志备份。

-- Assume the database is lost at this point. Now restore the full 
-- database. Specify the original full database backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed.
RESTORE DATABASE MyAdvWorks
   FROM MyAdvWorks_1
   WITH NORECOVERY
GO
-- Now restore the differential database backup, the second backup on 
-- the MyAdvWorks_1 backup device.
RESTORE DATABASE MyAdvWorks
   FROM MyAdvWorks_1
   WITH FILE = 2,
   NORECOVERY
GO
-- Now restore each transaction log backup created after
-- the differential database backup.
RESTORE LOG MyAdvWorks
   FROM MyAdvWorks_log1
   WITH NORECOVERY
GO
RESTORE LOG MyAdvWorks
   FROM MyAdvWorks_log2
   WITH RECOVERY
GO