还原事务日志备份 (SQL Server)Restore a Transaction Log 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 transaction log backup in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

先决条件Prerequisites

  • 备份必须按照其创建顺序进行还原。Backups must be restored in the order in which they were created. 在还原特定的事务日志备份之前,必须先还原下列以前备份,而不回滚未提交的事务,即 WITH NORECOVERY:Before you can restore a particular transaction log backup, you must first restore the following previous backups without rolling back uncommitted transactions, that is WITH NORECOVERY:

    • 在特定事务日志备份之前执行的完整数据库备份和上次差异备份(如果有)。The full database backup and the last differential backup, if any, taken before the particular transaction log backup. 创建最新的完整数据库备份或差异数据库备份之前,数据库必须使用完整恢复模式或大容量日志恢复模式。Before the most recent full or differential database backup was created, the database must have been using the full recovery model or bulk-logged recovery model.

    • 在完整数据库备份之后执行的所有事务日志备份或在特定事务日志备份之前执行的差异备份(如果您还原了差异备份)。All transaction log backups taken after the full database backup or the differential backup (if you restore one) and before the particular transaction log backup. 必须按照创建日志备份的顺序应用它们,并且日志链没有间隔。Log backups must be applied in the sequence in which they were created, without any gaps in the log chain.

      有关事务日志备份的详细信息,请参阅事务日志备份 (SQL Server)应用事务日志备份 (SQL Server)For more information about transaction log backups, see Transaction Log Backups (SQL Server) and Apply Transaction Log Backups (SQL Server).

SecuritySecurity

权限Permissions

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

警告

一般的还原过程需要在“还原数据库” 对话框中同时选择日志备份以及数据和差异备份。The normal process of a restore is to select the log backups in the Restore Database dialog box along with the data and differential backups.

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

  1. 连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。After connecting 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, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 右键单击该数据库,指向“任务” ,再指向“还原” ,然后单击“事务日志” ,这将打开“还原事务日志” 对话框。Right-click the database, point to Tasks, point to Restore, and then click Transaction Log, which opens the Restore Transaction Log dialog box.

    备注

    如果 “事务日志” 灰显,您可能需要首先还原完整备份或差异备份。If Transaction Log is grayed out, you may need to restore a full or differential backup first. 使用 “数据库” 备份对话框。Use the Database backup dialog box.

  4. “常规” 页上的 “数据库” 列表框中,选择数据库名称。On the General page, in the Database list box, select the name of a database. 仅列出处于还原状态的数据库。Only databases in the restoring state are listed.

  5. 若要指定要还原的备份集的源和位置,请单击以下选项之一:To specify the source and location of the backup sets to restore, click one of the following options:

    • 从数据库以前的备份From previous backups of 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.

    • 从文件或磁带From file or tape

      单击“浏览”按钮 ( ... ) 以打开“选择备份设备” 对话框。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.

  6. “选择要还原的事务日志备份” 网格中,选择要还原的备份。In the Select the transaction log backups to restore grid, select the backups to restore. 此网格列出了选定数据库可以使用的事务日志备份。This grid lists the transaction log backups available for the selected database. 只有在日志备份的 “第一个 LSN” 大于数据库的 “最后一个 LSN” 时,此日志备份才可用。A log backup is available only if its First LSN greater than the Last LSN of the database. 日志备份按照它们所包含的日志序列号 (LSN) 的顺序排列,并且也必须按照这种顺序还原。Log backups are listed in the order of the log sequence numbers (LSN) they contain, and they must be restored in this order.

    下表列出了网格的列标题并对列值进行了说明。The following table lists the column headers of the grid and describes their values.

    标题Header ReplTest1Value
    还原Restore 如果复选框处于选中状态,则指示要还原相应的备份集。Selected check boxes indicate the backup sets to be restored.
    名称Name 备份集的名称。Name of the backup set.
    组件Component 备份组件:“数据库”、“文件”或 <空白>(对于事务日志) 。Backed-up component: Database, File, or <blank> (for transaction logs).
    “数据库”Database 备份操作中涉及的数据库的名称。Name of the database involved in the backup operation.
    开始日期Start Date 备份操作开始的日期和时间(按客户端的区域设置显示)。Date and time when the backup operation began, presented in the regional setting of the client.
    完成日期Finish Date 备份操作完成的日期和时间(按客户端的区域设置显示)。Date and time when the backup operation finished, presented in the regional setting of the client.
    “第一个 LSN”First LSN 备份集中第一个事务的日志序列号。Log sequence number of the first transaction in the backup set. 对于文件备份为空。Blank for file backups.
    “最后一个 LSN”Last LSN 备份集中最后一个事务的日志序列号。Log sequence number of the last transaction in the backup set. 对于文件备份为空。Blank for file backups.
    检查点 LSNCheckpoint LSN 创建备份时最后一个检查点的日志序号。Log sequence number of the most recent checkpoint at the time the backup was created.
    完整 LSNFull LSN 最近的数据库完整备份的日志序列号。Log sequence number of the most recent full database backup.
    ServerServer 执行备份操作的数据库引擎实例的名称。Name of the Database Engine instance that performed the backup operation.
    用户名User Name 执行备份操作的用户的名称。Name of the user who performed the backup operation.
    SizeSize 备份集的大小(字节)。Size of the backup set in bytes.
    位置Position 备份集在卷中的位置。Position of the backup set in the volume.
    过期日期Expiration 备份集过期的日期和时间。Date and time the backup set expires.
  7. 选择下列选项之一:Select one of the following:

    • 时间点Point in time

      保留默认值(“最近状态” );或者通过单击“浏览”按钮,打开“时间点还原” 对话框,从中选择特定的日期和时间。Either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box.

    • 标记的事务Marked transaction

      将数据库还原为以前标记的事务。Restore the database to a previously marked transaction. 选择此选项会启动 “选择标记的事务” 对话框,从而显示一个网格,列出选定事务日志备份中可以使用的标记的事务。Selecting this option launches the Select Marked Transaction dialog box, which displays a grid listing the marked transactions available in the selected transaction log backups.

      默认情况下,将一直还原到(但不包含)标记的事务为止。By default, the restore is up to, but excluding, the marked transaction. 若要同时还原标记的事务,请选择 “包含标记的事务”To restore the marked transaction also, select Include marked transaction.

      下表列出了网格的列标题并对列值进行了说明。The following table lists the column headers of the grid and describes their values.

      标题Header ReplTest1Value
      <blank><blank> 显示一个用于选择标记的复选框。Displays a checkbox for selecting the mark.
      事务标记Transaction Mark 提交事务时,用户为标记的事务指定的名称。Name of the marked transaction specified by the user when the transaction was committed.
      DateDate 事务的提交日期及时间。Date and time of the transaction when it was committed. 事务日期和时间显示为 msdbgmarkhistory 表中所记录的日期和时间,而非客户端计算机的日期和时间。Transaction date and time are displayed as recorded in the msdbgmarkhistory table, not in the client computer's date and time.
      DescriptionDescription 提交事务时,用户为标记的事务指定的说明(如果有的话)。Description of marked transaction specified by the user when the transaction was committed (if any).
      LSNLSN 所标记事务的日志序列号。Log sequence number of the marked transaction.
      “数据库”Database 提交标记的事务时所在数据库的名称。Name of the database where the marked transaction was committed.
      用户名User Name 提交标记事务的数据库用户的名称。Name of the database user who committed the marked transaction.
  8. 若要查看或选择高级选项,请在 “选择页” 窗格中单击 “选项”To view or select the advanced options, click Options in the Select a page pane.

  9. “还原选项” 部分中,选项有:In the Restore options section, the choices are:

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

      将已发布的数据库还原到创建该数据库的服务器之外的服务器时,保留复制设置。Preserves the replication settings when restoring a published database to a server other than the server where the database was created.

      此选项只能与“回退未提交的事务,使数据库处于可以使用的状态...” 选项(等效于使用 RECOVERY 选项还原备份,将在后面予以介绍)一起使用。This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions... option (described later), which is equivalent to restoring a backup with the RECOVERY option.

      选择此选项等效于在 RESTORE 语句中使用 Transact-SQLTransact-SQLKEEP_REPLICATION 选项。Checking this option is equivalent to using the KEEP_REPLICATION option in a Transact-SQLTransact-SQLRESTORE statement.

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

      如果选中此选项,则在第一个备份集之后还原每个备份集之前,将显示“继续还原” 对话框,询问你是否要继续此还原顺序。Before restoring each backup set (after the first), this option brings up the Continue with Restore dialog box, which asks you to indicate whether you want to continue the restore sequence. 此对话框显示下一个介质集(如果可用)的名称、备份集的名称以及备份集的说明。This dialog displays the name of the next media set (if available), the backup set name, and backup set description.

      如果对于不同介质集必须更换磁带,则此选项特别有用。This option is particularly useful when you must swap tapes for different media sets. 例如,如果服务器只有一个磁带设备,则可以使用此选项。For example, you can use it when the server has only one tape device. 待您做好继续操作的准备后,再单击 “确定”Wait until you are ready to proceed before clicking OK.

      单击 “否” 将使数据库保持还原状态。Clicking No leaves the database in the restoring state. 完成上次还原之后,您可以在方便时继续按顺序还原。At your convenience, you can continue the restore sequence after the last restore that completed. 如果下一个备份是数据备份或差异备份,请再次使用 “还原数据库” 任务。If the next backup is a data or differential backup, use the Restore Database task again. 如果下一个备份是日志备份,请使用 “还原事务日志” 任务。If the next backup is a log backup, use the Restore Transaction Log task.

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

      使还原的数据库仅供 db_ownerdbcreatorsysadmin的成员使用。Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

      选择此选项等效于在 RESTORE 语句中使用 Transact-SQLTransact-SQLRESTRICTED_USER 选项。Checking this option is synonymous to using the RESTRICTED_USER option in a Transact-SQLTransact-SQLRESTORE statement.

  10. 对于 “恢复状态” 选项,请指定还原操作之后的数据库状态。For the Recovery state options, specify the state of the database after the restore operation.

    • 回退未提交的事务,使数据库处于可以使用的状态。无法还原其他事务日志。(RESTORE WITH RECOVERY)Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

      恢复数据库。Recovers the database. 此选项等效于 RESTORE 语句中的 Transact-SQLTransact-SQLRECOVERY 选项。This option is equivalent to the RECOVERY option in a Transact-SQLTransact-SQLRESTORE statement.

      请仅在没有要还原的日志文件时选择此选项。Choose this option only if you have no log files you want to restore.

    • 不对数据库执行任何操作,不回退未提交的事务。可以还原其他事务日志。(RESTORE WITH NORECOVERY)Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

      使数据库处于未恢复的 RESTORING 状态。Leaves the database unrecovered, in the RESTORING state. 此选项等效于在 RESTORE 语句中使用 Transact-SQLTransact-SQLNORECOVERY 选项。This option is equivalent to using the NORECOVERY option in a Transact-SQLTransact-SQLRESTORE statement.

      如果选择此选项, “保留复制设置” 选项将不可用。When you choose this option, the Preserve replication settings option is unavailable.

      重要

      对于镜像或辅助数据库,应始终选择此选项。For a mirror or secondary database, always select this option.

    • 使数据库处于只读模式。撤消未提交的事务,但将撤消操作保存在文件中,以便可使恢复效果逆转。(RESTORE WITH STANDBY)Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed. (RESTORE WITH STANDBY)

      使数据库处于备用状态。Leaves the database in a standby state. 此选项等效于在 RESTORE 语句中使用 Transact-SQLTransact-SQLSTANDBY 选项。This option is equivalent to using the STANDBY option in a Transact-SQLTransact-SQLRESTORE statement.

      选择此选项需要您指定一个备用文件。Choosing this option requires that you specify a standby file.

  11. 可选选项。如果选中此选项,请在 “备用文件” 文本框中指定备用文件的名称。Optionally, specify a standby file name in the Standby file text box. 如果您使数据库处于只读模式,则必须选中此选项。This option is required if you leave the database in read-only mode. 您可以浏览到该备用文件,也可以在文本框中键入其路径名。You can browse for the standby file or type its pathname in the text box.

使用 Transact-SQLUsing Transact-SQL

重要

我们建议您在每个 RESTORE 语句中显式指定 WITH NORECOVERY 或 WITH RECOVERY 以消除混淆。We recommend that you always explicitly specify either WITH NORECOVERY or WITH RECOVERY in every RESTORE statement to eliminate ambiguity. 在编写脚本时,这样做尤其重要。This is particularly important when writing scripts.

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

  1. 执行 RESTORE LOG 语句应用事务日志备份,同时指定:Execute the RESTORE LOG statement to apply the transaction log backup, specifying:

    • 事务日志将应用到的数据库的名称。The name of the database to which the transaction log will be applied.

    • 将从其中还原事务日志备份的备份设备。The backup device where the transaction log backup will be restored from.

    • NORECOVERY 子句。The NORECOVERY clause.

    此语句的基本语法如下:The basic syntax for this statement is as follows:

    RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY。RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY.

    其中,database_name 是数据库的名称,<backup_device> 是包含正在还原的日志备份的设备的名称。Where database_name is the name of database and <backup_device>is the name of the device that contains the log backup being restored.

  2. 对必须应用的每个事务日志备份重复步骤 1。Repeat step 1 for each transaction log backup you have to apply.

  3. 按照还原顺序还原了最后一个备份之后,可使用以下语句之一恢复数据库:After restoring the last backup in your restore sequence, to recover the database use one of the following statements:

    • 作为上一个 RESTORE LOG 语句的一部分恢复数据库:Recover the database as part of the last RESTORE LOG statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;  
      GO  
      
    • 等待使用单独的 RESTORE DATABASE 语句恢复数据库:Wait to recover the database by using a separate RESTORE DATABASE statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;   
      RESTORE DATABASE <database_name> WITH RECOVERY;  
      GO  
      

      通过等待恢复数据库,可以确认已还原所有必需的日志备份。Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. 执行时点还原时最好使用该方法。This approach is often advisable when you are performing a point-in-time restore.

    重要

    如果要创建镜像数据库,则省略恢复步骤。If you are creating a mirror database, omit the recovery step. 镜像数据库必须仍处于 RESTORING 状态。A mirror database must remain in the RESTORING state.

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

默认情况下, AdventureWorks2012AdventureWorks2012 数据库使用简单恢复模式。By default, the AdventureWorks2012AdventureWorks2012 database uses the simple recovery model. 以下示例要求修改数据库以使用完整恢复模式,如下所示:The following examples require modifying the database to use the full recovery model, as follows:

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  

A.A. 应用单个事务日志备份Applying a single transaction log backup

以下示例开始时使用名为 AdventureWorks2012AdventureWorks2012 备份设备上的完整数据库备份来还原 AdventureWorks2012_1数据库。The following example starts by restoring the AdventureWorks2012AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. 然后该示例应用名为 AdventureWorks2012_log备份设备上的第一个事务日志备份。The example then applies the first transaction log backup that resides on a backup device named AdventureWorks2012_log. 最后,该示例恢复数据库。Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2012  
   FROM AdventureWorks2012_1  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 1,  
   WITH NORECOVERY;  
GO  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

B.B. 应用多个事务日志备份Applying multiple transaction log backups

以下示例开始时使用名为 AdventureWorks2012AdventureWorks2012 备份设备上的完整数据库备份来还原 AdventureWorks2012_1数据库。The following example starts by restoring the AdventureWorks2012AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. 然后该示例逐一使用名为 AdventureWorks2012_log备份设备上的前三个事务日志备份。The example then applies, one by one, the first three transaction log backups that reside on a backup device named AdventureWorks2012_log. 最后,该示例恢复数据库。Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2012  
   FROM AdventureWorks2012_1  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 1,  
   NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 2,  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 3,  
   WITH NORECOVERY;  
GO  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

相关任务Related Tasks

另请参阅See Also

RESTORE (Transact-SQL) RESTORE (Transact-SQL)
应用事务日志备份 (SQL Server)Apply Transaction Log Backups (SQL Server)