您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

SQL Server 2014 虚拟机 (Resource Manager) 的自动备份Automated Backup for SQL Server 2014 Virtual Machines (Resource Manager)

自动备份会在运行 SQL Server 2014 Standard 或 Enterprise 的 Azure VM 上,自动为所有现有数据库和新数据库配置向 Microsoft Azure 的托管备份Automated Backup automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure VM running SQL Server 2014 Standard or Enterprise. 这样,便可以配置使用持久 Azure Blob 存储的定期数据库备份。This enables you to configure regular database backups that utilize durable Azure blob storage. 自动备份依赖于 SQL Server IaaS 代理扩展Automated Backup depends on the SQL Server IaaS Agent Extension.

备注

Azure 提供了可以用来创建和处理资源的两个不同部署模型:Azure 资源管理器部署模型和经典部署模型Azure has two different deployment models you can use to create and work with resources: Azure Resource Manager and classic. 本文介绍了资源管理器部署模型的使用。This article covers the use of the Resource Manager deployment model. 对于新部署,建议使用资源管理器部署模型而非经典部署模型。We recommend the Resource Manager deployment model for new deployments instead of the classic deployment model.

必备组件Prerequisites

若要使用自动备份,请考虑以下先决条件:To use Automated Backup, consider the following prerequisites:

操作系统Operating System:

  • Windows Server 2012Windows Server 2012
  • Windows Server 2012 R2Windows Server 2012 R2
  • Windows Server 2016Windows Server 2016

SQL Server 版本SQL Server version/edition:

  • SQL Server 2014 StandardSQL Server 2014 Standard
  • SQL Server 2014 EnterpriseSQL Server 2014 Enterprise

重要

自动备份适用于 SQL Server 2014。Automated Backup works with SQL Server 2014. 如果使用的是 SQL Server 2016/2017,可使用自动备份 v2 来备份数据库。If you are using SQL Server 2016/2017, you can use Automated Backup v2 to back up your databases. 有关详细信息,请参阅 Automated Backup v2 for SQL Server 2016 Azure Virtual Machines(适用于 SQL Server 2016 Azure 虚拟机的自动备份 v2)。For more information, see Automated Backup v2 for SQL Server 2016 Azure Virtual Machines.

数据库配置Database configuration:

  • 目标数据库必须使用完整恢复模式。Target databases must use the full recovery model. 有关对备份使用完整恢复模型产生的影响的详细信息,请参阅 Backup Under the Full Recovery Model(使用完整恢复模型的备份)。For more information about the impact of the full recovery model on backups, see Backup Under the Full Recovery Model.
  • 目标数据库必须位于默认 SQL Server 实例上。Target databases must be on the default SQL Server instance. SQL Server IaaS 扩展不支持命名的实例。The SQL Server IaaS Extension does not support named instances.

备注

自动备份依赖 SQL Server IaaS 代理扩展。Automated Backup relies on the SQL Server IaaS Agent Extension. 当前的 SQL 虚拟机库映像默认添加此扩展。Current SQL virtual machine gallery images add this extension by default. 有关详细信息,请参阅 SQL Server IaaS 代理扩展For more information, see SQL Server IaaS Agent Extension.

设置Settings

下表描述了可为自动备份配置的选项。The following table describes the options that can be configured for Automated Backup. 实际配置步骤根据你使用的是 Azure 门户还是 Azure Windows PowerShell 命令而有所不同。The actual configuration steps vary depending on whether you use the Azure portal or Azure Windows PowerShell commands.

设置Setting 范围(默认值)Range (Default) 描述Description
自动备份Automated Backup 启用/禁用(已禁用)Enable/Disable (Disabled) 为运行 SQL Server 2014 Standard 或 Enterprise 的 Azure VM 启用或禁用自动备份。Enables or disables Automated Backup for an Azure VM running SQL Server 2014 Standard or Enterprise.
保留期Retention Period 1-30 天(30 天)1-30 days (30 days) 保留备份的天数。The number of days to retain a backup.
存储帐户Storage Account Azure 存储帐户Azure storage account 用于在 Blob 存储中存储自动备份文件的 Azure 存储帐户。An Azure storage account to use for storing Automated Backup files in blob storage. 在此位置创建容器,用于存储所有备份文件。A container is created at this location to store all backup files. 备份文件命名约定包括日期、时间和计算机名称。The backup file naming convention includes the date, time, and machine name.
加密Encryption 启用/禁用(已禁用)Enable/Disable (Disabled) 启用或禁用加密。Enables or disables encryption. 启用加密时,用于还原备份的证书使用相同的命名约定存放在同一 automaticbackup 容器中的指定存储帐户内。When encryption is enabled, the certificates used to restore the backup are located in the specified storage account in the same automaticbackup container using the same naming convention. 如果密码发生更改,将使用该密码生成新证书,但旧证书在备份之前仍会还原。If the password changes, a new certificate is generated with that password, but the old certificate remains to restore prior backups.
密码Password 密码文本Password text 加密密钥的密码。A password for encryption keys. 仅当启用了加密时才需要此设置。This is only required if encryption is enabled. 若要还原加密的备份,必须具有创建该备份时使用的正确密码和相关证书。In order to restore an encrypted backup, you must have the correct password and related certificate that was used at the time the backup was taken.

在门户中进行配置Configure in the portal

可以在预配期间或针对现有的 SQL Server 2014 VM 使用 Azure 门户来配置自动备份。You can use the Azure portal to configure Automated Backup during provisioning or for existing SQL Server 2014 VMs.

配置新 VMConfigure new VMs

在 Resource Manager 部署模型中创建新的 SQL Server 2014 虚拟机时,可以使用 Azure 门户配置自动备份。Use the Azure portal to configure Automated Backup when you create a new SQL Server 2014 Virtual Machine in the Resource Manager deployment model.

SQL Server 设置"选项卡中,向下滚动到"自动备份",然后选择"启用"。In the SQL Server settings tab, scroll down to Automated backup and select Enable. 你还可以指定保留期和存储帐户,还可以启用加密、备份系统数据库以及配置备份计划。You can also specify the retention period, and storage account, as well as enabling encryption, backing up system databases, and configuring a backup schedule. 下面的 Azure 门户屏幕截图显示了“SQL 自动备份”设置。The following Azure portal screenshot shows the SQL Automated Backup settings.

Azure 门户中的 SQL 自动备份配置

配置现有 VMConfigure existing VMs

备注

以下屏幕截图来自 Azure 门户中的SQL 虚拟机资源。The following screenshots are from the SQL virtual machines resource within the Azure portal. 对于不在SQL VM 资源提供程序中注册的支持结束 (EOS) sql Server vm 和 SQL Server vm, 请改用SQL Server 配置选项卡来管理 SQL Server VM。For end-of-support (EOS) SQL server VMs, and SQL Server VMs that have not been registered with the SQL VM resource provider, use the SQL Server configuration tab to manage your SQL Server VM instead.

对于现有 SQL Server 虚拟机,请导航到SQL 虚拟机资源,然后选择 "备份"。For existing SQL Server virtual machines, navigate to the SQL virtual machines resource and then select Backups.

现有 VM 的 SQL 自动备份

完成后,选择 "备份" 页底部的 "应用" 按钮保存更改。When finished, select the Apply button on the bottom of the Backups page to save your changes.

首次启用自动备份时,Azure 会在后台配置 SQL Server IaaS 代理。If you are enabling Automated Backup for the first time, Azure configures the SQL Server IaaS Agent in the background. 在此期间,Azure 门户可能不会显示自动备份已配置。During this time, the Azure portal might not show that Automated Backup is configured. 请等待几分钟,以便安装和配置代理。Wait several minutes for the agent to be installed, configured. 之后,Azure 门户将反映新设置。After that the Azure portal will reflect the new settings.

备注

也可以使用模板来配置自动备份。You can also configure Automated Backup using a template. 有关详细信息,请参阅 Azure quickstart template for Automated Backup(用于自动备份的 Azure 快速入门模板)。For more information, see Azure quickstart template for Automated Backup.

使用 PowerShell 进行配置Configure with PowerShell

可使用 PowerShell 配置自动备份。You can use PowerShell to configure Automated Backup. 开始之前,必须:Before you begin, you must:

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

安装 SQL IaaS 扩展Install the SQL IaaS Extension

如果通过 Azure 门户预配了 SQL Server 虚拟机,应已安装 SQL Server IaaS 扩展。If you provisioned a SQL Server virtual machine from the Azure portal, the SQL Server IaaS Extension should already be installed. 可通过调用 Get-AzVM 命令并检查 Extensions 属性,确定是否为 VM 安装了该扩展。You can determine if it is installed for your VM by calling Get-AzVM command and examining the Extensions property.

$vmname = "vmname"
$resourcegroupname = "resourcegroupname"

(Get-AzVM -Name $vmname -ResourceGroupName $resourcegroupname).Extensions

如果已安装 SQL Server IaaS 代理扩展,应会看到列出的“SqlIaaSAgent”或“SQLIaaSExtension”。If the SQL Server IaaS Agent extension is installed, you should see it listed as “SqlIaaSAgent” or “SQLIaaSExtension”. 此外,该扩展的 ProvisioningState 应显示“Succeeded”。ProvisioningState for the extension should also show “Succeeded”.

如果未安装或未能预配该扩展,可使用以下命令来安装。If it is not installed or failed to be provisioned, you can install it with the following command. 除了 VM 名称和资源组以外,还必须指定 VM 所在的区域 ( $region)。In addition to the VM name and resource group, you must also specify the region ($region) that your VM is located in.

$region = "EASTUS2"
Set-AzVMSqlServerExtension -VMName $vmname `
    -ResourceGroupName $resourcegroupname -Name "SQLIaasExtension" `
    -Version "1.2" -Location $region

重要

如果尚未安装该扩展,安装该扩展将会重新启动 SQL Server 服务。If the extension is not already installed, installing the extension restarts the SQL Server service.

验证当前设置Verify current settings

如果在预配期间启用了自动备份,可以使用 PowerShell 检查当前配置。If you enabled automated backup during provisioning, you can use PowerShell to check your current configuration. 运行 Get-AzVMSqlServerExtension 命令并检查 AutoBackupSettings 属性:Run the Get-AzVMSqlServerExtension command and examine the AutoBackupSettings property:

(Get-AzVMSqlServerExtension -VMName $vmname -ResourceGroupName $resourcegroupname).AutoBackupSettings

应会看到类似于下面的输出:You should get output similar to the following:

Enable                      : False
EnableEncryption            : False
RetentionPeriod             : -1
StorageUrl                  : NOTSET
StorageAccessKey            : 
Password                    : 
BackupSystemDbs             : False
BackupScheduleType          : 
FullBackupFrequency         : 
FullBackupStartTime         : 
FullBackupWindowHours       : 
LogBackupFrequency          : 

如果输出显示 Enable 设置为 False,则必须启用自动备份。If your output shows that Enable is set to False, then you have to enable automated backup. 幸运的是,可通过相同的方式启用和配置自动备份。The good news is that you enable and configure Automated Backup in the same way. 有关信息,请参阅下一部分。See the next section for this information.

备注

如果在进行更改后立即检查设置,看到的可能是旧配置值。If you check the settings immediately after making a change, it is possible that you will get back the old configuration values. 请等待几分钟再检查设置,确保更改已应用。Wait a few minutes and check the settings again to make sure that your changes were applied.

配置自动备份Configure Automated Backup

随时可以使用 PowerShell 来启用自动备份以及修改其配置和行为。You can use PowerShell to enable Automated Backup as well as to modify its configuration and behavior at any time.

首先,为备份文件选择或创建存储帐户。First, select or create a storage account for the backup files. 以下脚本将选择一个存储帐户,或者创建一个存储帐户(如果不存在)。The following script selects a storage account or creates it if it does not exist.

$storage_accountname = "yourstorageaccount"
$storage_resourcegroupname = $resourcegroupname

$storage = Get-AzStorageAccount -ResourceGroupName $resourcegroupname `
    -Name $storage_accountname -ErrorAction SilentlyContinue
If (-Not $storage)
    { $storage = New-AzStorageAccount -ResourceGroupName $storage_resourcegroupname `
    -Name $storage_accountname -SkuName Standard_GRS -Location $region }

备注

自动备份不支持在高级存储中存储备份,但可以从使用高级存储的 VM 磁盘创建备份。Automated Backup does not support storing backups in premium storage, but it can take backups from VM disks which use Premium Storage.

然后,使用 New-AzVMSqlServerAutoBackupConfig 命令启用并配置自动备份设置,以便在 Azure 存储帐户中存储备份。Then use the New-AzVMSqlServerAutoBackupConfig command to enable and configure the Automated Backup settings to store backups in the Azure storage account. 在本示例中,备份保留 10 天。In this example, the backups are retained for 10 days. 第二个命令 Set-AzVMSqlServerExtension 使用这些设置更新指定的 Azure VM。The second command, Set-AzVMSqlServerExtension, updates the specified Azure VM with these settings.

$autobackupconfig = New-AzVMSqlServerAutoBackupConfig -Enable `
    -RetentionPeriodInDays 10 -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname

Set-AzVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

可能需要花费几分钟来安装和配置 SQL Server IaaS 代理。It could take several minutes to install and configure the SQL Server IaaS Agent.

备注

还有仅适用于 SQL Server 2016 和自动备份 v2 的其他 New-AzVMSqlServerAutoBackupConfig 设置。There are other settings for New-AzVMSqlServerAutoBackupConfig that apply only to SQL Server 2016 and Automated Backup v2. SQL Server 2014 不支持以下设置:BackupSystemDbs、BackupScheduleType、FullBackupFrequency、FullBackupStartHour、FullBackupWindowInHours 和 LogBackupFrequencyInMinutes。SQL Server 2014 does not support the following settings: BackupSystemDbs, BackupScheduleType, FullBackupFrequency, FullBackupStartHour, FullBackupWindowInHours, and LogBackupFrequencyInMinutes. 如果尝试在 SQL Server 2014 虚拟机上配置这些设置,则不存在错误,但不会应用这些设置。If you attempt to configure these settings on a SQL Server 2014 virtual machine, there is no error, but the settings do not get applied. 若要在 SQL Server 2016 虚拟机上使用这些设置,请参阅适用于 SQL Server 2016 Azure 虚拟机的自动备份 v2If you want to use these settings on a SQL Server 2016 virtual machine, see Automated Backup v2 for SQL Server 2016 Azure Virtual Machines.

要启用加密,请修改上述脚本,使其将 EnableEncryption 参数连同 CertificatePassword 参数的密码(安全字符串)一起传递。To enable encryption, modify the previous script to pass the EnableEncryption parameter along with a password (secure string) for the CertificatePassword parameter. 以下脚本启用上一示例中的自动备份设置,并添加加密。The following script enables the Automated Backup settings in the previous example and adds encryption.

$password = "P@ssw0rd"
$encryptionpassword = $password | ConvertTo-SecureString -AsPlainText -Force

$autobackupconfig = New-AzVMSqlServerAutoBackupConfig -Enable `
    -EnableEncryption -CertificatePassword $encryptionpassword `
    -RetentionPeriodInDays 10 -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname

Set-AzVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

若要确认是否应用了这些设置,请检查自动备份配置To confirm your settings are applied, verify the Automated Backup configuration.

禁用自动备份Disable Automated Backup

若要禁用自动备份,请运行同一个脚本,但不要为 New-AzVMSqlServerAutoBackupConfig 命令指定 -Enable 参数。To disable Automated Backup, run the same script without the -Enable parameter to the New-AzVMSqlServerAutoBackupConfig command. 缺少 -Enable 参数将向该命令发出指示以禁用此功能。The absence of the -Enable parameter signals the command to disable the feature. 与安装一样,可能需要花费几分钟时间来禁用自动备份。As with installation, it can take several minutes to disable Automated Backup.

$autobackupconfig = New-AzVMSqlServerAutoBackupConfig -ResourceGroupName $storage_resourcegroupname

Set-AzVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

示例脚本Example script

以下脚本提供一组可自定义的变量,用来为 VM 启用和配置自动备份。The following script provides a set of variables that you can customize to enable and configure Automated Backup for your VM. 根据具体的情况,可能需要根据要求自定义该脚本。In your case, you might need to customize the script based on your requirements. 例如,如果想要禁用系统数据库备份或启用加密,则必须更改该脚本。For example, you would have to make changes if you wanted to disable the backup of system databases or enable encryption.

$vmname = "yourvmname"
$resourcegroupname = "vmresourcegroupname"
$region = "Azure region name such as EASTUS2"
$storage_accountname = "storageaccountname"
$storage_resourcegroupname = $resourcegroupname
$retentionperiod = 10

# ResourceGroupName is the resource group which is hosting the VM where you are deploying the SQL IaaS Extension

Set-AzVMSqlServerExtension -VMName $vmname `
    -ResourceGroupName $resourcegroupname -Name "SQLIaasExtension" `
    -Version "1.2" -Location $region

# Creates/use a storage account to store the backups

$storage = Get-AzStorageAccount -ResourceGroupName $resourcegroupname `
    -Name $storage_accountname -ErrorAction SilentlyContinue
If (-Not $storage)
    { $storage = New-AzStorageAccount -ResourceGroupName $storage_resourcegroupname `
    -Name $storage_accountname -SkuName Standard_GRS -Location $region }

# Configure Automated Backup settings

$autobackupconfig = New-AzVMSqlServerAutoBackupConfig -Enable `
    -RetentionPeriodInDays $retentionperiod -StorageContext $storage.Context `
    -ResourceGroupName $storage_resourcegroupname

# Apply the Automated Backup settings to the VM

Set-AzVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
    -VMName $vmname -ResourceGroupName $resourcegroupname

监视Monitoring

可通过两种主要方式监视 SQL Server 2014 上的自动备份。To monitor Automated Backup on SQL Server 2014, you have two main options. 由于自动备份使用 SQL Server 托管备份功能,同样的监视方法对两者均适用。Because Automated Backup uses the SQL Server Managed Backup feature, the same monitoring techniques apply to both.

首先,可通过调用 msdb.smart_admin.sp_get_backup_diagnostics 轮询状态。First, you can poll the status by calling msdb.smart_admin.sp_get_backup_diagnostics. 或查询 msdb.smart_admin.fn_get_health_status 表值函数。Or query the msdb.smart_admin.fn_get_health_status table valued function.

备注

适用于 SQL Server 2014 中托管备份的架构是 msdb.smart_admin。The schema for Managed Backup in SQL Server 2014 is msdb.smart_admin. 在 SQL Server 2016 中,架构需更改为 msdb.managed_backup,且参考主题使用此较新架构。In SQL Server 2016 this changed to msdb.managed_backup, and the reference topics use this newer schema. 但对于 SQL Server 2014,必须为所有托管备份对象继续使用 smart_admin 架构。But for SQL Server 2014, you must continue to use the smart_admin schema for all Managed Backup objects.

另一种方式是利用内置的数据库邮件功能进行通知。Another option is to take advantage of the built-in Database Mail feature for notifications.

  1. 调用 msdb.smart_admin.sp_set_parameter 存储过程,向 SSMBackup2WANotificationEmailIds 参数分配电子邮件地址。Call the msdb.smart_admin.sp_set_parameter stored procedure to assign an email address to the SSMBackup2WANotificationEmailIds parameter.
  2. 启用 SendGrid,从 Azure VM 发送电子邮件。Enable SendGrid to send the emails from the Azure VM.
  3. 使用 SMTP 服务器和用户名配置数据库邮件。Use the SMTP server and user name to configure Database Mail. 可在 SQL Server Management Studio 中或使用 Transact-SQL 命令配置数据库邮件。You can configure Database Mail in SQL Server Management Studio or with Transact-SQL commands. 有关详细信息,请参阅数据库邮件For more information, see Database Mail.
  4. 配置 SQL Server 代理以使用数据库邮件Configure SQL Server Agent to use Database Mail.
  5. 验证是否通过本地 VM 防火墙和适用于 VM 的网络安全组允许该 SMTP 端口。Verify that the SMTP port is allowed both through the local VM firewall and the network security group for the VM.

后续步骤Next steps

自动备份会在 Azure VM 上配置托管备份。Automated Backup configures Managed Backup on Azure VMs. 因此,请务必查看有关 SQL Server 2014 托管备份的文档So it is important to review the documentation for Managed Backup on SQL Server 2014.

可以在下文中找到针对 Azure VM 上的 SQL Server 的其他备份和还原指导:Azure 虚拟机中 SQL Server 的备份和还原You can find additional backup and restore guidance for SQL Server on Azure VMs in the following article: Backup and Restore for SQL Server in Azure Virtual Machines.

有关其他可用自动化任务的信息,请参阅 SQL Server IaaS 代理扩展For information about other available automation tasks, see SQL Server IaaS Agent Extension.

有关在 Azure VM 中运行 SQL Server 的详细信息,请参阅 Azure 虚拟机中的 SQL Server 概述For more information about running SQL Server on Azure VMs, see SQL Server on Azure Virtual Machines overview.