将 SQL Server 数据库还原到某个时点(完整恢复模式)Restore a SQL Server Database to a Point in Time (Full Recovery Model)

适用对象:是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 Studio 将数据库还原到 Transact-SQLTransact-SQL中的某个时间点。This topic describes how to restore a database to a point in time in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 本主题仅与使用完整恢复模式或大容量日志恢复模式的 SQL ServerSQL Server 数据库有关。This topic is relevant only for SQL ServerSQL Server databases that use the full or bulk-logged recovery models.

重要

在大容量日志恢复模式下,如果日志备份包含大容量更改,则不能使用时点恢复方式恢复到该备份内的某个点。Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. 必须将数据库恢复到事务日志备份的结尾。The database must be recovered to the end of the transaction log backup.

开始之前Before You Begin

建议Recommendations

  • 使用 STANDBY 查找未知的时间点。Use STANDBY to find unknown point in time.

  • 在还原顺序中尽早指定时间点Specify the point in time early in a restore sequence

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 database to a point in time

  1. 在“对象资源管理器”中,连接到相应的 SQL Server 数据库引擎SQL Server Database Engine实例,然后展开服务器树。In Object Explorer, connect to the appropriate instance of the SQL Server 数据库引擎SQL Server Database Engine, and 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.

  6. 单击 “时间线” 以访问 “备份时间线” 对话框。Click Timeline to access the Backup Timeline dialog box.

  7. “还原到” 部分中,单击 “具体日期和时间”In the Restore to section, click Specific date and time.

  8. 使用 “日期”“时间” 框或滑动条来指定应停止还原的具体日期和时间。Use either the Date and Time boxes or the slider bar to specify a specific date and time to where the restore should stop. 单击“确定”。 Click OK.

    备注

    使用“时间线间隔” 框更改时间线上显示的时间量。Use the Timeline Interval box to change the amount of time displayed on the timeline.

  9. 指定具体时点后,数据库恢复顾问确保只有需要还原到该时点的那些备份在 “要还原的备份集” 网格的 “还原” 列中处于选中状态。After you have specified a specific point in time, the Database Recovery Advisor ensures that only backups that are required for restoring to that point in time are selected in the Restore column of the Backup sets to restore grid. 这些选定的备份构成了为您的时点还原建议的还原计划。These selected backups make up the recommended restore plan for your point-in-time restore. 应当仅使用选定的备份进行时点还原操作。You should use only the selected backups for your point-in-time restore operation.

    有关“用于还原的备份集” 网格中的列的信息,请参阅还原数据库(“常规”页)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page). 有关数据库恢复顾问的信息,请参阅还原和恢复概述 (SQL Server)For information about the Database Recovery Advisor, see Restore and Recovery Overview (SQL Server).

  10. “选项” 页的 “还原选项” 面板中,可以根据您的实际情况选择下列任意选项: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)

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

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

  11. “恢复状态” 框选择一个选项。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).

  12. 如果对于选择的时间点是必需的,则选择“还原前进行结尾日志备份” 。Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected. 无需修改此设置,但可以选择备份日志尾部(即使不需要)。You do not need to modify this setting, but you can choose to backup the tail of the log even if it is not required.

  13. 如果存在与数据库的活动连接,则还原操作可能会失败。Restore operations may 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. 此复选框可在执行还原操作之前将数据库设置为单用户模式,并在该操作完成后将数据库设置为多用户模式。This check box sets the database to single user mode before performing the restore operations, and sets the database to multi-user mode when complete.

  14. 如果要在每个还原操作之间进行提示,请选择 “还原每个备份之前进行提示”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.

使用 Transact-SQLUsing Transact-SQL

Before you beginBefore you begin

始终从日志备份还原到指定时间。A specified time is always restored from a log backup. 在还原序列的每个 RESTORE LOG 语句中,必须在相同的 STOPAT 子句中指定目标时间或事务。In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT clause. 作为时点还原的先决条件,必须首先还原其端点早于目标还原时间的完整数据库备份。As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time. 只要您之后还原每个随后日志备份(到达和包括包含目标时间点的日志备份),该完整数据库备份就可以早于最近的完整数据库备份。That full database backup can be older than the most recent full database backup as long as you then restore every subsequent log backup, up to and including the log backup that contains your target point in time.

如果数据备份太临近指定的目标时间,而需帮助识别要还原哪个数据库备份,则可以在 RESTORE DATABASE 语句中可选地指定 WITH STOPAT 子句以引发错误。To help you identify which database backup to restore, you can optionally specify your WITH STOPAT clause in your RESTORE DATABASE statement to raise an error if a data backup is too recent for the specified target time. 始终会还原完整数据备份,即使该数据备份包含目标时间也同样如此。The complete data backup is always restored, even if it contains the target time.

基本 Transact-SQLTransact-SQL 语法Basic Transact-SQLTransact-SQL syntax

RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY… RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY...

恢复点是在 time 指定的 datetime值或之前发生的最新的事务提交。The recovery point is the latest transaction commit that occurred at or before the datetime value that is specified by time.

若要只还原在特定时间点之前所做的修改,请为还原的每个备份指定 WITH STOPAT = timeTo restore only the modifications that were made before a specific point in time, specify WITH STOPAT = time for each backup you restore. 这样确保了不会超出目标时间。This makes sure that you do not go past the target time.

将数据库还原到时间点To restore a database to a point in time

备注

有关此过程的示例,请参阅本节后面的 示例 (Transact-SQL)For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. 连接到您要还原数据库的服务器实例。Connect to server instance on which you want to restore the database.

  2. 执行使用 NORECOVERY 选项的 RESTORE DATABASE 语句。Execute the RESTORE DATABASE statement using the NORECOVERY option.

    备注

    如果部分还原顺序不包括任何 FILESTREAM 文件组,则不支持时间点还原。If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. 可以强制该还原顺序以继续执行操作。You can force the restore sequence to continue. 但在 RESTORE 语句中省略的 FILESTREAM 文件组将永远无法还原。However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. 若要强制执行时点还原,请指定 CONTINUE_AFTER_ERROR 选项以及 STOPAT、STOPATMARK 或 STOPBEFOREMARK 选项,还必须在随后的 RESTORE LOG 语句中指定后面的三个选项。To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. 如果指定 CONTINUE_AFTER_ERROR,则部分还原顺序将成功,但 FILESTREAM 文件组将不可恢复。If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

  3. 还原上次差异数据库备份(如果有),而不恢复数据库 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。Restore the last differential database backup, if any, without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  4. 以创建事务日志备份的相同顺序应用每个事务日志备份,同时指定要停止还原日志的时间 (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT = time , RECOVERY)。Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT**=time,** RECOVERY).

    备注

    RECOVERY 和 STOPAT 选项。The RECOVERY and STOPAT options. 如果事务日志备份不包含要求的时间(例如,如果指定的时间超出了事务日志所包含的时间范围),则会生成警告,并且不会恢复数据库。If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

示例 (Transact-SQL)Example (Transact-SQL)

下面的示例将数据库还原到它在 12:00 AMApril 15, 2020 的状态,并显示涉及多个日志备份的还原操作。The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple log backups. 在备份设备上,要还原的完整数据库备份 AdventureWorksBackups是设备上的第三个备份集 (FILE = 3),第一个日志备份是第四个备份集 (FILE = 4),第二个日志备份是第五个备份集 (FILE = 5)。On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

重要

AdventureWorks2012AdventureWorks2012 数据库使用简单恢复模式。The AdventureWorks2012AdventureWorks2012 database uses the simple recovery model. 若要允许日志备份,请在完整备份数据库之前,使用 ALTER DATABASE AdventureWorks SET RECOVERY FULL将数据库设置为使用完整恢复模式。To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE AdventureWorks SET RECOVERY FULL.

RESTORE DATABASE AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=3, NORECOVERY;  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO  
  

相关任务Related Tasks

另请参阅See Also

backupset (Transact-SQL) backupset (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)RESTORE HEADERONLY (Transact-SQL)