还原 Azure VM 上的 SQL Server 数据库Restore SQL Server databases on Azure VMs

本文介绍如何还原 Azure 虚拟机 (VM) 上运行的、已由 Azure 备份服务备份到 Azure 备份恢复服务保管库的 SQL Server 数据库。This article describes how to restore a SQL Server database that's running on an Azure virtual machine (VM) that the Azure Backup service has backed up to an Azure Backup Recovery Services vault.

本文介绍如何还原 SQL Server 数据库。This article describes how to restore SQL Server databases. 有关详细信息,请参阅备份 Azure VM 上的 SQL Server 数据库For more information, see Back up SQL Server databases on Azure VMs.

还原到某个时间点或恢复点Restore to a time or a recovery point

Azure 备份可以还原 Azure VM 上运行的 SQL Server 数据库,如下所述:Azure Backup can restore SQL Server databases that are running on Azure VMs as follows:

  • 使用事务日志备份还原到特定的日期或时间(精确到秒)。Restore to a specific date or time (to the second) by using transaction log backups. Azure 备份可自动确定相应的完整备份、差异备份和日志链备份,这些是根据所选时间进行还原所必需的。Azure Backup automatically determines the appropriate full differential backup and the chain of log backups that are required to restore based on the selected time.
  • 还原特定的完整备份或差异备份,这样就可以还原到特定的恢复点。Restore a specific full or differential backup to restore to a specific recovery point.

先决条件Prerequisites

在还原数据库之前,请注意以下事项:Before you restore a database, note the following:

  • 可将数据库还原到同一 Azure 区域中的 SQL Server 实例。You can restore the database to an instance of a SQL Server in the same Azure region.
  • 目标服务器必须注册到与源服务器相同的保管库。The destination server must be registered to the same vault as the source.
  • 若要将 TDE 加密的数据库还原到另一个 SQL Server,需先将证书还原到目标服务器To restore a TDE-encrypted database to another SQL Server, you need to first restore the certificate to the destination server.
  • 在还原“master”数据库之前,请使用启动选项 -m AzureWorkloadBackup 在单用户模式下启动 SQL Server 实例。Before you restore the "master" database, start the SQL Server instance in single-user mode by using the startup option -m AzureWorkloadBackup.
    • -m 的值是客户端的名称。The value for -m is the name of the client.
    • 只能使用指定的客户端名称打开连接。Only the specified client name can open the connection.
  • 对于所有系统数据库(模型数据库、master 数据库、msdb 数据库),请在触发还原操作之前停止 SQL Server 代理服务。For all system databases (model, master, msdb), stop the SQL Server Agent service before you trigger the restore.
  • 关闭任何可能尝试与其中任何数据库建立连接的应用程序。Close any applications that might try to take a connection to any of these databases.
  • 如果服务器上有多个实例在运行,所有实例都应启动并运行,否则,该服务器不会显示在可将数据库还原到的目标服务器列表中。If you have multiple instances running on a server, all of the instances should be up and running otherwise the server would not appear in the list of destination servers for you to restore database to.

还原数据库Restore a database

若要进行还原,需要以下权限:To restore, you need the following permissions:

  • 在其中执行还原的保管库中的“备份操作员”权限。 Backup Operator permissions in the vault where you're doing the restore.
  • 对已备份的源 VM 的参与者(写入) 访问权限。Contributor (write) access to the source VM that's backed up.
  • 对目标 VM 的参与者(写入) 访问权限:Contributor (write) access to the target VM:
    • 若要还原到同一 VM,则此项将是源 VM。If you're restoring to the same VM, this is the source VM.
    • 若要还原到备用位置,则此项将是新的目标 VM。If you're restoring to an alternate location, this is the new target VM.

按如下所述进行还原:Restore as follows:

  1. 打开在其中注册 SQL Server VM 的保管库。Open the vault in which the SQL Server VM is registered.

  2. 在保管库仪表板的“使用情况”下,选择“备份项” 。On the vault dashboard, under Usage, select Backup Items.

  3. 在“备份项”中的“备份管理类型”下,选择“Azure VM 中的 SQL”。 In Backup Items, under Backup Management Type, select SQL in Azure VM.

    选择“Azure VM 中 SQL”

  4. 选择要还原的数据库。Select the database to restore.

    选择要还原的数据库

  5. 查看数据库菜单。Review the database menu. 它提供有关数据库备份的信息,其中包括:It provides information about the database backup, including:

    • 最旧和最新的还原点。The oldest and latest restore points.
    • 处于完整和批量日志记录恢复模式的、且已配置事务日志备份的数据库在过去 24 小时的日志备份状态。The log backup status for the last 24 hours for databases that are in full and bulk-logged recovery mode and that are configured for transactional log backups.
  6. 选择“还原”。 Select Restore.

    选择“还原”

  7. 在“还原配置”中,指定要将数据还原到何处(或如何还原): In Restore Configuration, specify where (or how) to restore the data:

    • 备用位置:将数据库还原到备用位置,同时保留原始源数据库。Alternate Location: Restore the database to an alternate location and keep the original source database.
    • 覆盖 DB:将数据还原到原始源所在的同一 SQL Server 实例。Overwrite DB: Restore the data to the same SQL Server instance as the original source. 此选项将覆盖原始数据库。This option overwrites the original database.

    重要

    如果选定的数据库属于 Always On 可用性组,则 SQL Server 不允许覆盖数据库。If the selected database belongs to an Always On availability group, SQL Server doesn't allow the database to be overwritten. 仅“备用位置”可用。Only Alternate Location is available.

    • 作为文件还原:不是作为数据库还原,而是以后使用 SQL Server Management Studio 在包含备份文件的计算机上,还原可作为数据库恢复的备份文件。Restore as files: Instead of restoring as a database, restore the backup files that can be recovered as a database later on any machine where the files are present using SQL Server Management Studio. “还原配置”菜单Restore Configuration menu

还原到备用位置Restore to an alternate location

  1. 在“还原配置”菜单中的“还原位置”下,选择“备用位置”。 In the Restore Configuration menu, under Where to Restore, select Alternate Location.

  2. 选择要将数据库还原到其中的 SQL Server 名称和实例。Select the SQL Server name and instance to which you want to restore the database.

  3. 在“还原数据库名称”框中,输入目标数据库的名称。 In the Restored DB Name box, enter the name of the target database.

  4. 如果适用,请选择“当选定的 SQL 实例上已存在同名的 DB 时覆盖”。 If applicable, select Overwrite if the DB with the same name already exists on selected SQL instance.

  5. 选择“确定” 。Select OK.

    为“还原配置”菜单提供值

  6. 在“选择还原点”中,选择是要还原到特定的时间点,还是还原到特定的恢复点In Select restore point, select whether to restore to a specific point in time or to restore to a specific recovery point.

    备注

    时间点还原仅适用于采用完整和批量日志记录恢复模式的数据库日志备份。The point-in-time restore is available only for log backups for databases that are in full and bulk-logged recovery mode.

还原并覆盖Restore and overwrite

  1. 在“还原配置”菜单中的“还原位置”下,选择“覆盖数据库” > “确定”。 In the Restore Configuration menu, under Where to Restore, select Overwrite DB > OK.

    选择“覆盖数据库”

  2. 在“选择还原点”中选择“日志(时间点)”,以还原到特定的时间点In Select restore point, select Logs (Point in Time) to restore to a specific point in time. 或者选择“完整和差异”以还原到特定的恢复点Or select Full & Differential to restore to a specific recovery point.

    备注

    时间点还原仅适用于采用完整和批量日志记录恢复模式的数据库日志备份。The point-in-time restore is available only for log backups for databases that are in full and bulk-logged recovery mode.

作为文件还原Restore as files

若要将备份数据作为 .bak 文件而不是数据库还原,请选择“作为文件还原”。 To restore the backup data as .bak files instead of a database, choose Restore as Files. 将文件转储到指定的路径后,可将这些文件放在要将其作为数据库还原到的任何计算机上。Once the files are dumped to a specified path, you can take these files to any machine where you want to restore them as a database. 由于可将这些文件移到任何计算机,因此现在可以跨订阅和区域还原数据。By the virtue of being able to move these files around to any machine, you can now restore the data across subscriptions and regions.

  1. 在“还原配置”菜单中的“还原位置”下,选择“作为文件还原”。 In the Restore Configuration menu, under Where to Restore, select Restore as files.
  2. 选择要将备份还原到的 SQL Server 名称。Select the SQL Server name to which you want to restore the backup files.
  3. 在“服务器上的目标路径”中,输入在步骤 2 中选择的服务器上的文件夹路径。 In the Destination path on the server input the folder path on the server selected in step 2. 此位置是服务要将全部所需备份文件转储到的位置。This is the location where the service will dump all the necessary backup files. 通常,将网络共享路径或已装载的 Azure 文件共享的路径指定为目标路径,可让同一网络中的其他计算机或者装载了相同 Azure 文件共享的计算机更轻松地访问这些文件。Typically, a network share path, or path of a mounted Azure file share when specified as the destination path, enables easier access to these files by other machines in the same network or with the same Azure file share mounted on them.
  4. 选择“确定” 。Select OK.

选择“作为文件还原”

  1. 选择与所有可用 .bak 文件要还原到的时间对应的还原点Select the Restore Point corresponding to which all the available .bak files will be restored.

选择还原点

  1. 与所选恢复点关联的所有备份文件将转储到目标路径中。All the backup files associated with the selected recovery point are dumped into the destination path. 可以使用 SQL Server Management Studio 在包含这些文件的任何计算机上将其作为数据库还原。You can restore the files as a database on any machine they are present on using SQL Server Management Studio.

已在目标路径中还原备份文件

还原到特定时间点Restore to a specific point in time

如果已选择“日志(时间点)”作为还原类型,请执行以下操作: If you've selected Logs (Point in Time) as the restore type, do the following:

  1. 在“还原日期/时间”下,打开日历。 Under Restore Date/Time, open the calendar. 在“日历”中,包含恢复点的日期以粗体显示,当前日期已突出显示。On the calendar, the dates that have recovery points are displayed in bold type, and the current date is highlighted.

  2. 选择包含恢复点的日期。Select a date that has recovery points. 不能选择没有恢复点的日期。You can't select dates that have no recovery points.

    打开日历

  3. 选择日期后,时间线图会显示连续范围内的可用恢复点。After you select a date, the timeline graph displays the available recovery points in a continuous range.

  4. 在时间线图表中指定恢复时间,或选择一个时间。Specify a time for the recovery on the timeline graph, or select a time. 然后选择“确定”。 Then select OK.

    选择还原点

  5. 在“高级配置”菜单上,若要使数据库在还原后保持不可运行状态,请启用“使用 NORECOVERY 还原”。 On the Advanced Configuration menu, if you want to keep the database nonoperational after the restore, enable Restore with NORECOVERY.

  6. 若要更改目标服务器上的还原位置,请输入新的目标路径。If you want to change the restore location on the destination server, enter a new target path.

  7. 选择“确定” 。Select OK.

    高级配置菜单

  8. 在“还原”菜单中,选择“还原”启动还原作业。 On the Restore menu, select Restore to start the restore job.

  9. 在“通知”区域中跟踪还原进度,或者在数据库菜单中选择“还原作业”来跟踪进度。 Track the restore progress in the Notifications area, or track it by selecting Restore jobs on the database menu.

    还原作业的进度

还原到特定还原点Restore to a specific restore point

如果已选择“完整和差异”作为还原类型,请执行以下操作: If you've selected Full & Differential as the restore type, do the following:

  1. 在列表中选择一个恢复点,然后选择“确定”完成还原点过程。 Select a recovery point from the list, and select OK to complete the restore point procedure.

    选择完整恢复点

  2. 在“高级配置”菜单上,若要使数据库在还原后保持不可运行状态,请启用“使用 NORECOVERY 还原”。 On the Advanced Configuration menu, if you want to keep the database nonoperational after the restore, enable Restore with NORECOVERY.

  3. 若要更改目标服务器上的还原位置,请输入新的目标路径。If you want to change the restore location on the destination server, enter a new target path.

  4. 选择“确定” 。Select OK.

    高级配置菜单

  5. 在“还原”菜单中,选择“还原”启动还原作业。 On the Restore menu, select Restore to start the restore job.

  6. 在“通知”区域中跟踪还原进度,或者在数据库菜单中选择“还原作业”来跟踪进度。 Track the restore progress in the Notifications area, or track it by selecting Restore jobs on the database menu.

    还原作业的进度

还原包含大量文件的数据库Restore databases with large number of files

如果数据库中文件的总字符串大小超过特定的限制,Azure 备份会将数据库文件列表存储在不同的 PIT 组件中,因此在执行还原操作期间无法设置目标还原路径。If the total string size of files in a database is greater than a particular limit, Azure Backup stores the list of database files in a different pit component such that you will not be able to set the target restore path during the restore operation. 文件将还原到 SQL 默认路径。The files will be restored to the SQL default path instead.

还原包含大文件的数据库

后续步骤Next steps

管理和监视 Azure 备份服务备份的 SQL Server 数据库。Manage and monitor SQL Server databases that are backed up by Azure Backup.