SQL Server 2014 虚拟机(资源管理器)的自动备份Automated Backup for SQL Server 2014 virtual machines (Resource Manager)

适用于: Azure VM 上的 SQL Server

自动备份会在运行 SQL Server 2014 Standard 或 Enterprise 的 Azure VM 上自动为所有现有数据库和新数据库配置托管备份到 AzureAutomated Backup automatically configures Managed Backup to 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 infrastructure as a service (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.


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

操作系统Operating system :

  • Windows Server 2012 及更高版本Windows Server 2012 and greater

SQL Server 版本SQL Server version/edition :

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


有关 SQL 2016 及更高版本,请参阅 SQL Server 2016 的自动备份For SQL 2016 and greater, see Automated Backup for SQL Server 2016.

数据库配置Database configuration :

  • 目标用户数据库必须使用完整恢复模式。Target user databases must use the full recovery model. 系统数据库不需要使用完整恢复模型。System databases do not have to use the full recovery model. 但是,如果需要为模型或 MSDB 创建日志备份,则必须使用完整恢复模型。However, if you require log backups to be taken for Model or MSDB, you must use 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 IaaS 代理扩展Automated backup relies on the full SQL Server IaaS Agent Extension. 因此,只有默认实例或单个命名实例的目标数据库支持自动备份。As such, automated backup is only supported on target databases from the default instance, or a single named instance. 如果没有默认实例,并且存在多个命名实例,则 SQL IaaS 扩展将失败,自动备份将无法工作。If there is no default instance, and multiple named instances, the SQL IaaS extension fails and automated backup will not work.


下表描述了可为自动备份配置的选项。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.

配置新 VMConfigure new VMs

在资源管理器部署模型中创建新的 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 设置”选项卡上,向下滚动到“自动备份”,然后选择“启用” 。On the SQL Server settings tab, scroll down to Automated backup and select Enable . 下面的 Azure 门户屏幕截图显示了“SQL 自动备份”设置。The following Azure portal screenshot shows the SQL Automated Backup settings.

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

配置现有 VMConfigure existing VMs

对于现有的 SQL Server 虚拟机,请选择 SQL Server 虚拟机。For existing SQL Server virtual machines, select your SQL Server virtual machine. 然后选择 VM“设置”的“SQL Server 配置”部分 。Then select the SQL Server configuration section of the VM Settings .

在“SQL Server 配置”窗格的“自动备份”部分,单击“编辑”按钮 。In the SQL Server configuration pane, click the Edit button in the Automated backup section.

现有 VM 的 SQL 自动备份

完成后,选择“SQL Server 配置”设置底部的“确定”按钮保存更改 。When finished, select the OK button on the bottom of the SQL Server configuration settings 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 and 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 Server IaaS 扩展Install the SQL Server IaaS Extension

如果通过 Azure 门户预配了 SQL Server VM,应已安装 SQL Server IaaS 扩展。If you provisioned a SQL Server VM from the Azure portal, the SQL Server IaaS Extension should already be installed. 可通过调用 Get-AzVM 命令并检查 Extensions 属性,确定是否为 VM 安装了该扩展 。You can determine whether 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”应显示“成功”。ProvisioningState for the extension should also show "Succeeded."

如果未安装或未能预配该扩展,可使用以下命令进行安装。If it is not installed or it has 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. 指定 SQL Server VM 的许可类型,通过 Azure 混合权益在标准预付费套餐或自带许可之间进行选择。Specify the license type for your SQL Server VM, choosing between either Standard Pay-in-Advance Offer or bring-your-own-license via the Azure Hybrid Benefit.

New-AzSqlVM  -Name $vmname `
    -ResourceGroupName $resourcegroupname `
    -Location $region -LicenseType <PAYG/AHUB>


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

验证当前设置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 China East 2"
$storage_accountname = "storageaccountname"
$storage_resourcegroupname = $resourcegroupname
$retentionperiod = 10

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

Set-AzVMSqlServerExtension -VMName $vmname `
    -ResourceGroupName $resourcegroupname -Name "SQLIaasExtension" `
    -Version "2.0" -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


可通过一种方式监视 SQL Server 2014 上的自动备份。To monitor Automated Backup on SQL Server 2014, you have one option. 由于自动备份使用 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 轮询状态。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.

后续步骤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 on 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.