还原差异数据库备份 (SQL Server)Restore a Differential Database Backup (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 ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中还原差异数据库备份。This topic describes how to restore a differential database backup in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 不允许在显式或隐式事务中使用 RESTORE。RESTORE is not allowed in an explicit or implicit transaction.

  • 无法在早期版本的 SQL ServerSQL Server 中还原较新版本的 SQL ServerSQL Server创建的备份。Backups that are created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

  • SQL ServerSQL Server中,可以从使用 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 或更高版本创建的数据库备份来还原用户数据库。In SQL ServerSQL Server, you can restore a user database from a database backup that was created by using SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version.

先决条件Prerequisites

  • 在完整恢复模式或大容量日志恢复模式下,必须先备份活动事务日志(称为日志尾部),然后才能还原数据库。Under the full or bulk-logged recovery model, before you can restore a database, you must back up the active transaction log (known as the tail of the log). 有关详细信息,请参阅 备份事务日志 (SQL Server)数据库还原到一个新位置并且可以选择重命名该数据库。For more information, see Back Up a Transaction Log (SQL Server).

SecuritySecurity

权限Permissions

如果不存在要还原的数据库,则用户必须有 CREATE DATABASE 权限才能执行 RESTORE。If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. 如果数据库存在,则 RESTORE 权限默认授予 sysadmindbcreator 固定服务器角色成员以及数据库的所有者 (dbo)(对于 FROM DATABASE_SNAPSHOT 选项,数据库始终存在)。If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE 权限被授予那些成员身份信息始终可由服务器使用的角色。RESTORE permissions are given to roles in which membership information is always readily available to the server. 因为只有在固定数据库可以访问且没有损坏时(在执行 RESTORE 时并不会总是这样)才能检查固定数据库角色成员身份,所以 db_owner 固定数据库角色成员没有 RESTORE 权限。Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

使用 SQL Server Management StudioUsing SQL Server Management Studio

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

  1. 连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。After you connect to the appropriate instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展开 “数据库”Expand Databases. 根据具体的数据库,选择一个用户数据库,或展开“系统数据库”并选择一个系统数据库。Depending on the database, either select a user database or expand System Databases, and then select a system database.

  3. 右键单击数据库,指向“任务” ,再指向“还原” ,然后单击“数据库” 。Right-click the database, point to Tasks, point to Restore, and then click Database.

  4. “常规” 页上,使用 “源” 部分指定要还原的备份集的源和位置。On the General page, use the Source section to specify the source and location of the backup sets to restore. 选择以下选项之一:Select one of the following options:

    • “数据库”Database

      从下拉列表中选择要还原的数据库。Select the database to restore from the drop-down list. 此列表仅包含已根据 msdb 备份历史记录进行备份的数据库。The list contains only databases that have been backed up according to the msdb backup history.

    备注

    如果备份是从另一台服务器执行的,则目标服务器不具有指定数据库的备份历史记录信息。If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. 这种情况下,请选择 “设备” 以手动指定要还原的文件或设备。In this case, select Device to manually specify the file or device to restore.

    • “设备”Device

      单击“浏览”按钮 ( ... ) 以打开“选择备份设备” 对话框。Click the browse (...) button to open the Select backup devices dialog box. “备份介质类型” 框中,从列出的设备类型中选择一种。In the Backup media type box, select one of the listed device types. 若要为 “备份介质” 框选择一个或多个设备,请单击 “添加”To select one or more devices for the Backup media box, click Add.

      将所需设备添加到 “备份介质” 列表框后,单击 “确定” 返回到 “常规” 页。After you add the devices you want to the Backup media list box, click OK to return to the General page.

      在“源:设备:数据库”列表框中,选择应还原的数据库名称 。In the Source: Device: Database list box, select the name of the database which should be restored.

      注意 :此列表仅在选择了 “设备” 时才可用。Note This list is only available when Device is selected. 只有在所选设备上具有备份的数据库才可用。Only databases that have backups on the selected device will be available.

  5. “目标” 部分中, “数据库” 框自动填充要还原的数据库的名称。In the Destination section, the Database box is automatically populated with the name of the database to be restored. 若要更改数据库名称,请在 “数据库” 框中输入新名称。To change the name of the database, enter the new name in the Database box.

    备注

    若要在特定的时间点停止还原,请单击 “时间线” 以访问 “备份时间线” 对话框。To stop the restore at a specific point in time, click Timeline to access the Backup Timeline dialog box. 有关在特定时间点停止数据库还原的帮助,请参阅将 SQL Server 数据库还原到某个时点(完整恢复模式)For help with stopping a database restore at a specific point in time, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

  6. “要还原的备份集” 网格中,选择要通过差异备份还原的备份。In the Backup sets to restore grid, select the backups through the differential backup that you wish to restore.

    有关“用于还原的备份集” 网格中的列的信息,请参阅还原数据库(“常规”页)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page).

  7. “选项” 页的 “还原选项” 面板中,可以根据您的实际情况选择下列任意选项:On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:

    • 覆盖现有数据库(WITH REPLACE)Overwrite the existing database (WITH REPLACE)

    • 保留复制设置(WITH KEEP_REPLICATION)Preserve the replication settings (WITH KEEP_REPLICATION)

    • 还原每个备份之前进行提示Prompt before restoring each backup

    • 限制对还原数据库的访问(WITH RESTRICTED_USER)Restrict access to the restored database (WITH RESTRICTED_USER)

    有关这些选项的详细信息,请参阅还原数据库(“选项”页)For more information about these options, see Restore Database (Options Page).

  8. “恢复状态” 框选择一个选项。Select an option for the Recovery state box. 此框确定还原操作之后的数据库状态。This box determines the state of the database after the restore operation.

    • RESTORE WITH RECOVERY 是默认行为,它通过回滚未提交的事务,使数据库处于可以使用的状态。RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. 无法还原其他事务日志。Additional transaction logs cannot be restored. 如果您要立即还原所有必要的备份,则选择此选项。Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY 不对数据库执行任何操作,不回滚未提交的事务。RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. 可以还原其他事务日志。Additional transaction logs can be restored. 除非恢复数据库,否则无法使用数据库。The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY 使数据库处于只读模式。RESTORE WITH STANDBY which leaves the database in read-only mode. 它撤消未提交的事务,但将撤消操作保存在备用文件中,以便能够还原恢复结果。It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

    有关这些选项的说明,请参阅还原数据库(“选项”页)For descriptions of the options, see Restore Database (Options Page).

  9. 如果存在与数据库的活动连接,则还原操作将失败。Restore operations will fail if there are active connections to the database. 选中 “关闭现有连接” 以确保关闭 Management StudioManagement Studio 和数据库之间的所有活动连接。Check the Close existing connections option to ensure that all active connections between Management StudioManagement Studio and the database are closed.

  10. 如果要在每个还原操作之间进行提示,请选择 “还原每个备份之前进行提示”Select Prompt before restoring each backup if you wish to be prompted between each restore operation. 除非数据库过大并且您要监视还原操作的状态,否则通常没有必要选中该选项。This is not usually necessary unless the database is large and you wish to monitor the status of the restore operation.

  11. 可以使用 “文件” 页将数据库还原到一个新位置。Optionally, use the Files page to restore the database to a new location. 有关移动数据库的帮助,请参阅将数据库还原到新位置 (SQL Server)For help with moving a database, see Restore a Database to a New Location (SQL Server).

  12. 单击“确定”。 Click OK.

使用 Transact-SQLUsing Transact-SQL

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

  1. 执行 RESTORE DATABASE 语句并指定 NORECOVERY 子句,以还原在差异数据库备份之前执行的完整数据库备份。Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the full database backup that comes before the differential database backup. 有关详细信息,请参阅操作说明:还原完整备份For more information, see How to: Restore a Full Backup.

  2. 执行 RESTORE DATABASE 语句以还原差异数据库备份,同时指定:Execute the RESTORE DATABASE statement to restore the differential database backup, specifying:

    • 要应用差异数据库备份的数据库的名称。The name of the database to which the differential database backup is applied.

    • 从其中还原差异数据库备份的备份设备。The backup device where the differential database backup is restored from.

    • NORECOVERY 子句,前提是在还原差异数据库备份之后,还要应用事务日志备份。The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored. 否则应指定 RECOVERY 子句。Otherwise, specify the RECOVERY clause.

  3. 通过完整恢复模式或大容量日志恢复模式,还原差异数据库备份可将数据库还原到差异数据库备份完成的点。With the full or bulk-logged recovery model, restoring a differential database backup restores the database to the point at which the differential database backup was completed. 若要恢复到故障点,在创建完最后一个差异数据库备份之后,必须应用所有已创建的事务日志备份。To recover to the point of failure, you must apply all transaction log backups created after the last differential database backup was created. 有关详细信息,请参阅应用事务日志备份 (SQL Server)For more information, see Apply Transaction Log Backups (SQL Server).

示例 (Transact-SQL)Examples (Transact-SQL)

A.A. 还原差异数据库备份Restoring a differential database backup

以下示例将还原 MyAdvWorks 数据库及其差异数据库备份。This example restores a database and differential database backup of the MyAdvWorks database.

-- 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.B. 还原数据库、差异数据库以及事务日志备份Restoring a database, differential database, and transaction log backup

以下示例将还原 MyAdvWorks 数据库及其差异数据库和事务日志备份。This example restores a database, differential database, and transaction log backup of the MyAdvWorks database.

-- 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  

相关任务Related Tasks

另请参阅See Also

差异备份 (SQL Server) Differential Backups (SQL Server)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)