在 Azure VM 中备份 SQL Server 数据库Back up a SQL Server database in an Azure VM

本教程介绍如何将 Azure VM 上运行的 SQL Server 数据库备份到 Azure 备份恢复服务保管库。This tutorial shows you how to back up a SQL Server database running on an Azure VM to an Azure Backup Recovery Services vault. 在本文中,学习如何:In this article, you learn how to:

  • 创建并配置保管库。Create and configure a vault.
  • 发现数据库并设置备份。Discover databases, and set up backups.
  • 为数据库设置自动保护。Set up auto-protection for databases.
  • 运行按需备份。Run an on-demand backup.

先决条件Prerequisites

在备份 SQL Server 数据库之前,请检查以下条件:Before you back up your SQL Server database, check the following conditions:

  1. 在托管 SQL Server 实例的 VM 所在的区域或位置标识或创建一个恢复服务保管库。Identify or create a Recovery Services vault in the same region or locale as the VM hosting the SQL Server instance.
  2. 检查备份 SQL 数据库所需的 VM 权限Check the VM permissions needed to back up the SQL databases.
  3. 验证 VM 是否已建立网络连接Verify that the VM has network connectivity.
  4. 检查是否根据 Azure 备份的命名准则命名了 SQL Server 数据库。Check that the SQL Server databases are named in accordance with naming guidelines for Azure Backup.
  5. 验证是否未为该数据库启用了其他任何备份解决方案。Verify that you don't have any other backup solutions enabled for the database. 在设置此方案之前,请禁用其他所有 SQL Server 备份。Disable all other SQL Server backups before you set up this scenario. 可以同时针对某个 Azure VM 以及该 VM 上运行的 SQL Server 数据库启用 Azure 备份,而不会发生任何冲突。You can enable Azure Backup for an Azure VM along with Azure Backup for a SQL Server database running on the VM without any conflict.

建立网络连接Establish network connectivity

对于所有操作,SQL Server VM 需要与 Azure 中国 IP 地址建立连接。For all operations, the SQL Server VM virtual machine needs connectivity to Azure China IP addresses. 如果未连接到公共 IP 地址,VM 操作(数据库发现、配置备份、计划备份、还原恢复点等)将失败。VM operations (database discovery, configure backups, schedule backups, restore recovery points, and so on) fail without connectivity to the public IP addresses. 使用以下选项之一建立连接:Establish connectivity with one of these options:

  • 允许 Azure 数据中心 IP 范围:允许下载中的 IP 范围Allow the Azure datacenter IP ranges: Allow the IP ranges in the download. 若要访问网络安全组 (NSG),请使用 Set-AzureNetworkSecurityRule cmdlet。To access network security group (NSG), use the Set-AzureNetworkSecurityRule cmdlet.
  • 部署用于路由流量的 HTTP 代理服务器:在 Azure VM 中备份 SQL Server 数据库时,该 VM 上的备份扩展将使用 HTTPS API 将管理命令发送到 Azure 备份,并将数据发送到 Azure 存储。Deploy an HTTP proxy server to route traffic: When you back up a SQL Server database on an Azure VM, the backup extension on the VM uses the HTTPS APIs to send management commands to Azure Backup, and data to Azure Storage. 备份扩展还使用 Azure Active Directory (Azure AD) 进行身份验证。The backup extension also uses Azure Active Directory (Azure AD) for authentication. 通过 HTTP 代理路由这三个服务的备份扩展流量。Route the backup extension traffic for these three services through the HTTP proxy. 该扩展是为了访问公共 Internet 而配置的唯一组件。The extensions are the only component that's configured for access to the public internet.

每个选项各有其优缺点Each option has advantages and disadvantages

选项Option 优点Advantages 缺点Disadvantages
允许 IP 范围Allow IP ranges 无额外成本。No additional costs. 管理起来很复杂,因为 IP 地址范围随时会变化。Complex to manage because the IP address ranges change over time.

允许访问整个 Azure,而不只是 Azure 存储。Provides access to the whole of Azure, not just Azure Storage.
使用 HTTP 代理Use an HTTP proxy 允许在代理中对存储 URL 进行精细控制。Granular control in the proxy over the storage URLs is allowed.

在单个位置通过 Internet 访问 VM。Single point of internet access to VMs.

不受 Azure IP 地址变化的影响。Not subject to Azure IP address changes.
通过代理软件运行 VM 带来的额外成本。Additional costs to run a VM with the proxy software.

设置 VM 权限Set VM permissions

当你为 SQL Server 数据库配置备份时,Azure 备份会执行许多操作:Azure Backup does a number of things when you configure backup for a SQL Server database:

  • 添加 AzureBackupWindowsWorkload 扩展。Adds the AzureBackupWindowsWorkload extension.
  • 为了发现虚拟机上的数据库,Azure 备份会创建帐户 NT SERVICE\AzureWLBackupPluginSvc 。To discover databases on the virtual machine, Azure Backup creates the account NT SERVICE\AzureWLBackupPluginSvc. 此帐户用于备份和还原,需要拥有 SQL sysadmin 权限。This account is used for backup and restore, and requires SQL sysadmin permissions.
  • Azure 备份利用 NT AUTHORITY\SYSTEM 帐户进行数据库发现/查询,因此此帐户需是 SQL 上的公共登录名。Azure Backup leverages the NT AUTHORITY\SYSTEM account for database discovery/inquiry, so this account need to be a public login on SQL.

如果 SQL Server VM 不是从 Azure 市场创建的,你可能会收到错误 UserErrorSQLNoSysadminMembership。If you didn't create the SQL Server VM from Azure Marketplace, you might receive an error UserErrorSQLNoSysadminMembership. 如果发生此错误,请遵照这些说明予以解决。If this occurs follow these instructions.

验证 Azure 备份的数据库命名准则Verify database naming guidelines for Azure Backup

避免在数据库名称中使用以下字符:Avoid the following for database names:

  • 尾随/前导空格Trailing/Leading spaces
  • 尾随“!”Trailing ‘!’
  • 右方括号“]”Close square bracket ‘]’
  • 数据库名称以“F:\”开头Databases names starting with ‘F:\’

对于 Azure 表不支持的字符,可以使用别名,但我们建议避免使用别名。We do have aliasing for Azure table unsupported characters, but we recommend avoiding them. 了解详细信息Learn more.

创建恢复服务保管库Create a Recovery Services vault

恢复服务保管库是用于存储在不同时间创建的备份和恢复点的实体。A Recovery Services vault is an entity that stores the backups and recovery points created over time. 恢复服务保管库还包含与受保护虚拟机关联的备份策略。The Recovery Services vault also contains the backup policies that are associated with the protected virtual machines.

若要创建恢复服务保管库,请执行以下操作:To create a Recovery Services vault:

  1. Azure 门户中登录到自己的订阅。Sign in to your subscription in the Azure portal.

  2. 在左侧菜单中,选择“所有服务”。On the left menu, select All services.

    选择“所有服务”

  3. 在“所有服务”对话框中,输入“恢复服务”。In the All services dialog box, enter Recovery Services. 资源列表根据输入进行筛选。The list of resources filters according to your input. 在资源列表中,选择“恢复服务保管库”。In the list of resources, select Recovery Services vaults.

    输入并选择“恢复服务保管库”

    此时会显示订阅中的恢复服务保管库列表。The list of Recovery Services vaults in the subscription appears.

  4. 在“恢复服务保管库”仪表板上,选择“添加”。On the Recovery Services vaults dashboard, select Add.

    添加恢复服务保管库

    此时会打开“恢复服务保管库”对话框。The Recovery Services vault dialog box opens. 提供“名称”、“订阅”、“资源组”和“位置”的值。Provide values for the Name, Subscription, Resource group, and Location.

    配置恢复服务保管库

    • 名称:输入一个友好名称以标识此保管库。Name: Enter a friendly name to identify the vault. 名称对于 Azure 订阅必须是唯一的。The name must be unique to the Azure subscription. 指定的名称应至少包含 2 个字符,最多不超过 50 个字符。Specify a name that has at least two, but not more than 50 characters. 名称必须以字母开头且只能包含字母、数字和连字符。The name must start with a letter and consist only of letters, numbers, and hyphens.

    • 订阅:选择要使用的订阅。Subscription: Choose the subscription to use. 如果你仅是一个订阅的成员,则会看到该名称。If you're a member of only one subscription, you'll see that name. 如果不确定要使用哪个订阅,请使用默认的(建议的)订阅。If you're not sure which subscription to use, use the default (suggested) subscription. 仅当工作或学校帐户与多个 Azure 订阅关联时,才会显示多个选项。There are multiple choices only if your work or school account is associated with more than one Azure subscription.

    • 资源组:使用现有资源组或创建新组。Resource group: Use an existing resource group or create a new one. 要查看订阅中可用的资源组列表,请选择“使用现有资源”,然后从下拉列表框中选择一个资源。To see the list of available resource groups in your subscription, select Use existing, and then select a resource from the drop-down list box. 若要创建新资源组,请选择“新建”,然后输入名称。To create a new resource group, select Create new and enter the name. 有关资源组的完整信息,请参阅 Azure 资源管理器概述For complete information about resource groups, see Azure Resource Manager overview.

    • 位置:选择保管库的地理区域。Location: Select the geographic region for the vault. 要创建保管库以保护虚拟机,保管库必须与虚拟机位于同一区域中。To create a vault to protect virtual machines, the vault must be in the same region as the virtual machines.

      重要

      如果不确定 VM 的位置,请关闭对话框。If you're not sure of the location of your VM, close the dialog box. 转到门户中的虚拟机列表。Go to the list of virtual machines in the portal. 如果虚拟机位于多个区域,请在每个区域中创建一个恢复服务保管库。If you have virtual machines in several regions, create a Recovery Services vault in each region. 先在第一个位置创建保管库,然后再为其他位置创建保管库。Create the vault in the first location, before you create the vault for another location. 无需指定存储帐户即可存储备份数据。There's no need to specify storage accounts to store the backup data. 恢复服务保管库和 Azure 备份服务会自动处理这种情况。The Recovery Services vault and the Azure Backup service handle that automatically.

  5. 准备好创建恢复服务保管库后,选择“创建”。When you're ready to create the Recovery Services vault, select Create.

    创建恢复服务保管库

    创建恢复服务保管库可能需要一段时间。It can take a while to create the Recovery Services vault. 可在门户右上角“通知”区域监视状态通知。Monitor the status notifications in the Notifications area at the upper-right corner of the portal. 创建保管库后,它会显示在“恢复服务保管库”的列表中。After your vault is created, it's visible in the list of Recovery Services vaults. 如果未看到创建的保管库,请选择“刷新”。If you don't see your vault, select Refresh.

    刷新备份保管库列表

发现 SQL Server 数据库Discover SQL Server databases

发现 VM 上运行的数据库。Discover databases running on the VM.

  1. Azure 门户中,打开用于备份数据库的恢复服务保管库。In the Azure portal, open the Recovery Services vault you use to back up the database.

  2. 在“恢复服务保管库”仪表板上选择“备份” 。On the Recovery Services vault dashboard, select Backup.

    选择“+备份”打开“备份目标”菜单

  3. 在“备份目标”中,将“工作负荷的运行位置”设置为“Azure”(默认值)。 In Backup Goal, set Where is your workload running to Azure (the default).

  4. 在“要备份哪些内容”中,选择“Azure VM 中的 SQL Server”。 In What do you want to backup, select SQL Server in Azure VM.

    选择“Azure VM 中的 SQL Server”进行备份

  5. 在“备份目标” > “发现 VM 中的数据库”中,选择“开始发现”以搜索订阅中不受保护的 VM。 In Backup Goal > Discover DBs in VMs, select Start Discovery to search for unprotected VMs in the subscription. 搜索过程需要花费一段时间,具体取决于订阅中不受保护的虚拟机数量。It can take a while, depending on the number of unprotected virtual machines in the subscription.

    • 发现后,未受保护的 VM 应会按名称和资源组列在列表中。Unprotected VMs should appear in the list after discovery, listed by name and resource group.

    • 如果某个 VM 未按预期列出,请检查它是否已保管库中备份。If a VM isn't listed as you expect, check whether it's already backed up in a vault.

    • 可能有多个 VM 同名,但它们属于不同的资源组。Multiple VMs can have the same name but they'll belong to different resource groups.

      在搜索 VM 中的数据库期间,备份将会挂起。

  6. 在 VM 列表中,选择运行 SQL Server 数据库的VM,然后选择“发现数据库”。 In the VM list, select the VM running the SQL Server database > Discover DBs.

  7. 在“通知”区域跟踪数据库发现。 Track database discovery in the Notifications area. 完成该作业可能需要一段时间,具体取决于 VM 上的数据库数量。It can take a while for the job to complete, depending on how many databases are on the VM. 发现选定的数据库后,会显示一条成功消息。When the selected databases are discovered, a success message appears.

    部署成功消息

  8. Azure 备份将发现该 VM 上的所有 SQL Server 数据库。Azure Backup discovers all SQL Server databases on the VM. 在发现期间,后台将发生以下情况:During discovery, the following occurs in the background:

    • Azure 备份将 VM 注册到用于备份工作负荷的保管库。Azure Backup register the VM with the vault for workload backup. 已注册 VM 上的所有数据库只能备份到此保管库。All databases on the registered VM can only be backed up to this vault.

    • Azure 备份在 VM 上安装 AzureBackupWindowsWorkload 扩展。Azure Backup installs the AzureBackupWindowsWorkload extension on the VM. 不会在 SQL 数据库中安装任何代理。No agent is installed on the SQL database.

    • Azure 备份在 VM 上创建服务帐户 NT Service\AzureWLBackupPluginSvcAzure Backup creates the service account NT Service\AzureWLBackupPluginSvc on the VM.

      • 所有备份和还原操作使用该服务帐户。All backup and restore operations use the service account.
      • NT Service\AzureWLBackupPluginSvc 需要 SQL sysadmin 权限。NT Service\AzureWLBackupPluginSvc needs SQL sysadmin permissions. 在 Azure 市场中创建的所有 SQL Server VM 已预装 SqlIaaSExtension。All SQL Server VMs created in Azure Marketplace come with the SqlIaaSExtension installed. AzureBackupWindowsWorkload 扩展使用 SQLIaaSExtension 自动获取所需的权限。The AzureBackupWindowsWorkload extension uses the SQLIaaSExtension to automatically get the required permissions.
    • 如果 VM 不是从市场创建的,则该 VM 上未安装 SqlIaaSExtension,并且发现操作将会失败并出现错误消息 UserErrorSQLNoSysAdminMembershipIf you didn't create the VM from the marketplace, then the VM doesn't have the SqlIaaSExtension installed, and the discovery operation fails with the error message UserErrorSQLNoSysAdminMembership. 请按照说明解决此问题。Follow the instructions to fix this issue.

      选择 VM 和数据库

配置备份Configure backup

按如下所述配置备份:Configure backup as follows:

  1. 在“备份目标”中,选择“配置备份”。 In Backup Goal, select Configure Backup.

    选择“配置备份”

  2. 选择“配置备份”,将显示“选择要备份的项”窗格 。Select Configure Backup, the Select items to backup pane appears. 这将列出所有已注册的可用性组和独立的 SQL Server。This lists all the registered availability groups and standalone SQL Servers. 展开行左侧的 V 形图标,以查看该实例或 Always on AG 中所有未受保护的数据库。Expand the chevron to the left of the row to see all the unprotected databases in that instance or Always on AG.

    显示包含独立数据库的所有 SQL Server 实例

  3. 选择要保护的所有数据库,然后选择“确定”。 Select all the databases you want to protect > OK.

    保护数据库

    为了优化备份负载,Azure 备份会将一个备份作业中的最大数据库数目设置为 50。To optimize backup loads, Azure Backup sets a maximum number of databases in one backup job to 50.

    • 或者,可以通过在“自动保护”列中的相应下拉列表内选择“打开”选项,针对整个实例或 Always On 可用性组启用自动保护 。Alternatively, you can enable auto-protection on the entire instance or Always On Availability group by selecting the ON option in the corresponding dropdown in the AUTOPROTECT column. 自动保护功能不仅可以一次性针对所有现有数据库启用保护,而且还会自动保护将来要添加到该实例或可用性组的所有新数据库。The auto-protection feature not only enables protection on all the existing databases in one go but also automatically protects any new databases that will be added to that instance or the availability group in future.
  4. 选择“确定”以打开“备份策略”窗格 。Select OK to open the Backup policy pane.

    针对 Always On 可用性组启用自动保护

  5. 在“选择备份策略”中选择一个策略,然后选择“确定”。 In Choose backup policy, select a policy, then select OK.

    • 选择默认策略:HourlyLogBackup。Select the default policy: HourlyLogBackup.

    • 选择前面为 SQL 创建的现有备份策略。Choose an existing backup policy previously created for SQL.

    • 根据 RPO 和保留范围定义新策略。Define a new policy based on your RPO and retention range.

      选择“备份策略”

  6. 在“备份”菜单生,选择“启用备份” 。On Backup menu, select Enable backup.

    启用选定的备份策略

  7. 在门户的“通知”区域跟踪配置进度。 Track the configuration progress in the Notifications area of the portal.

    通知区域

创建备份策略Create a backup policy

备份策略定义备份创建时间以及这些备份的保留时间。A backup policy defines when backups are taken and how long they're retained.

  • 策略是在保管库级别创建的。A policy is created at the vault level.
  • 多个保管库可以使用相同的备份策略,但必须向每个保管库应用该备份策略。Multiple vaults can use the same backup policy, but you must apply the backup policy to each vault.
  • 创建备份策略时,每日完整备份是默认设置。When you create a backup policy, a daily full backup is the default.
  • 可以添加差异备份,但仅在将完整备份配置为每周发生时,才能这样做。You can add a differential backup, but only if you configure full backups to occur weekly.
  • 了解不同类型的备份策略。Learn about different types of backup policies.

创建备份策略:To create a backup policy:

  1. 在保管库中,选择“备份策略” > “添加”。 In the vault, select Backup policies > Add.

  2. 在“添加”菜单中,选择“Azure VM 中的 SQL Server”以定义策略类型。 In Add menu, select SQL Server in Azure VM to define the policy type.

    为新的备份策略选择策略类型

  3. 在“策略名称”处输入新策略的名称 。In Policy name, enter a name for the new policy.

  4. 在“完整备份策略”中选择“备份频率”,然后选择“每日”或“每周”。 In Full Backup policy, select a Backup Frequency, choose Daily or Weekly.

    • 如果选择“每日”,请选择备份作业开始时的小时和时区。 For Daily, select the hour and time zone when the backup job begins.

    • 必须运行完整备份,因为不能禁用“完整备份”选项 。You must run a full backup as you can't turn off the Full Backup option.

    • 选择“完整备份”以查看策略。Select Full Backup to view the policy.

    • 对于每日完整备份,无法创建差异备份。You can't create differential backups for daily full backups.

    • 如果选择“每周”,请选择备份作业开始时的星期、小时和时区。 For Weekly, select the day of the week, hour, and time zone when the backup job begins.

      新备份策略字段

  5. 对于“保留范围”,默认已选择所有选项。 For Retention Range, by default all options are selected. 清除你不需要的所有保留范围限制,并设置要使用的间隔。Clear any undesired retention range limits you don't want to use, and set the intervals to use.

    • 任何类型的备份(完整/差异/日志)的最短保持期均为七天。Minimum retention period for any type of backup (full/differential/log) is seven days.
    • 恢复点已根据其保留范围标记为保留。Recovery points are tagged for retention based on their retention range. 例如,如果选择每日完整备份,则每天只触发一次完整备份。For example, if you select a daily full backup, only one full backup is triggered each day.
    • 根据每周保留范围和每周保留设置,将会标记并保留特定日期的备份。The backup for a specific day is tagged and retained based on the weekly retention range and your weekly retention setting.
    • 每月和每年保留范围的行为类似。The monthly and yearly retention ranges behave in a similar way.

    保留范围间隔设置

  6. 在“完整备份策略”菜单中,选择“确定”接受设置。 In the Full Backup policy menu, select OK to accept the settings.

  7. 若要添加差异备份策略,请选择“差异备份”。 To add a differential backup policy, select Differential Backup.

    保留范围间隔设置 打开差异备份策略菜单Retention range interval settings Open the differential backup policy menu

  8. 在“差异备份策略”中,选择“启用”打开频率和保留控件。 In Differential Backup policy, select Enable to open the frequency and retention controls.

    • 每天最多可以触发一次差异备份。At most, you can trigger one differential backup per day.
    • 差异备份最多可以保留 180 天。Differential backups can be retained for a maximum of 180 days. 如果需要保留更长时间,必须使用完整备份。If you need longer retention, you must use full backups.
  9. 选择“确定”保存策略,并返回“备份策略”主菜单。 Select OK to save the policy and return to the main Backup policy menu.

  10. 若要添加事务日志备份策略,请选择“日志备份”。 To add a transactional log backup policy, select Log Backup.

  11. 在“日志备份”中选择“启用”,并设置频率和保留控件。 In Log Backup, select Enable, and then set the frequency and retention controls. 日志备份最多可以每隔 15 分钟发生一次,最多可以保留 35 天。Log backups can occur as often as every 15 minutes, and can be retained for up to 35 days.

  12. 选择“确定”保存策略,并返回“备份策略”主菜单。 Select OK to save the policy and return to the main Backup policy menu.

    编辑日志备份策略

  13. 在“备份策略”菜单中,选择是否启用“SQL 备份压缩”。 On the Backup policy menu, choose whether to enable SQL Backup Compression.

    • 默认已禁用压缩。Compression is disabled by default.
    • Azure 备份在后端使用 SQL 本机备份压缩。On the back end, Azure Backup uses SQL native backup compression.
  14. 完成备份策略的编辑后,选择“确定”。 After you complete the edits to the backup policy, select OK.

运行按需备份Run an on-demand backup

  1. 在恢复服务保管库中,选择“备份项目”。In your Recovery Services vault, choose Backup items.
  2. 选择“Azure VM 中的 SQL”。Select "SQL in Azure VM".
  3. 右键单击数据库,并选择“立即备份”。Right-click on a database, and choose "Backup now".
  4. 选择“备份类型”(完整/差异/日志/仅复制完整)和“压缩”(启用/禁用)Choose the Backup Type (Full/Differential/Log/Copy Only Full) and Compression (Enable/Disable)
  5. 选择“确定”,开始备份。Select OK to begin the backup.
  6. 通过转到恢复服务保管库并选择“备份作业”来监视备份作业。Monitor the backup job by going to your Recovery Services vault and choosing "Backup Jobs".

后续步骤Next steps

本教程使用 Azure 门户执行了以下操作:In this tutorial, you used the Azure portal to:

  • 创建并配置保管库。Create and configure a vault.
  • 发现数据库并设置备份。Discover databases, and set up backups.
  • 为数据库设置自动保护。Set up auto-protection for databases.
  • 运行按需备份。Run an on-demand backup.

继续阅读下一教程,学习如何从磁盘还原 Azure 虚拟机。Continue to the next tutorial to restore an Azure virtual machine from disk.