Azure 虚拟机中 SQL Server 的备份和还原Backup and Restore for SQL Server in Azure Virtual Machines

本文提供有关适用于 Azure Windows 虚拟机中运行的 SQL Server 的备份和还原选项的指导。This article provides guidance on the backup and restore options available for SQL Server running in a Windows virtual machine in Azure. Azure 存储维护每个 Azure VM 磁盘的三个副本,以确保数据不会丢失或物理数据不会损坏。Azure Storage maintains three copies of every Azure VM disk to guarantee protection against data loss or physical data corruption. 因此,与在本地不同,无需重点关注硬件故障问题。Thus, unlike on-premises, you don't need to focus on hardware failures. 但是,仍应备份 SQL Server 数据库,以防止发生应用程序或用户错误,例如意外的数据插入或删除。However, you should still backup your SQL Server databases to protect against application or user errors, such as inadvertent data insertions or deletions. 如果出现这种意外,必须能够还原到特定的时间点。In this situation, it is important to be able to restore to a specific point in time.

本文的第一部分提供可用备份和还原选项的概述。The first part of this article provides an overview of the available backup and restore options. 后续部分提供有关每种策略的详细信息。This is followed by sections that provide more information on each strategy.

备份和还原选项Backup and restore options

下表提供有关适用于 Azure VM 上运行的 SQL Server 的各种备份和还原选项的信息:The following table provides information on various backup and restore options for SQL Server running on Azure VMs:

策略Strategy SQL 版本SQL versions 说明Description
自动备份Automated Backup 20142014
使用自动备份可以针对 SQL Server VM 上的所有数据库计划定期备份。Automated Backup allows you to schedule regular backups for all databases on a SQL Server VM. 备份在 Azure 存储中最多存储 30 天。Backups are stored in Azure storage for up to 30 days. 从 SQL Server 2016 开始,自动备份 v2 提供更多选项,例如,配置手动计划,以及完整备份和日志备份的频率。Beginning with SQL Server 2016, Automated Backup v2 offers additional options such as configuring manual scheduling and the frequency of full and log backups.
适用于 SQL VM 的 Azure 备份Azure Backup for SQL VMs 20082008
Azure 备份为 Azure VM 中运行的 SQL Server 提供企业级备份功能。Azure Backup provides an Enterprise class backup capability for SQL Server running in Azure VMs. 使用此服务,可以集中管理多个服务器和数千个数据库的备份。With this service, you can centrally manage backups for multiple servers and thousands of databases. 可在门户中将数据库还原到特定的时间点。Databases can be restored to a specific point in time in the portal. 此服务提供可将备份保留数年之久的可自定义保留策略。It offers a customizable retention policy that can maintain backups for years.
手动备份Manual backup 全部All 根据所用的 SQL Server 版本,可通过不同的方法手动备份和还原 Azure VM 上运行的 SQL Server。Depending on your version of SQL Server, there are various techniques to manually backup and restore SQL Server running on an Azure VM. 在这种情况下,你需要负责指定数据库的备份方式和存储位置,并管理这些备份。In this scenario, you are responsible for how your databases are backed up and the storage location and management of these backups.

以下部分更详细地介绍了每个选项。The following sections describe each option in more detail. 本文的最后一个部分以功能矩阵的形式提供了摘要。The final section of this article provides a summary in the form of a feature matrix.

自动备份Automated Backup

自动备份为 Azure Windows VM 中运行的 SQL Server Standard 和 Enterprise 版本提供自动备份服务。Automated Backup provides an automatic backup service for SQL Server Standard and Enterprise editions running in a Windows VM in Azure. 此服务由 SQL Server IaaS 代理扩展提供。该扩展已自动安装在 Azure 门户中的 SQL Server Windows 虚拟机映像上。This service is provided by the SQL Server IaaS Agent Extension, which is automatically installed on SQL Server Windows virtual machine images in the Azure portal.

所有数据库将备份到配置的 Azure 存储帐户中。All databases are backed up to an Azure storage account that you configure. 备份可以加密,最多会保留 30 天。Backups can be encrypted and retained for up to 30 days.

SQL Server 2016 和更高版本的 VM 提供更多的自定义选项,以及自动备份 v2。SQL Server 2016 and higher VMs offer more customization options with Automated Backup v2. 这些改进包括:These improvements include:

  • 系统数据库备份System database backups
  • 手动备份计划和时间窗口Manual backup schedule and time window
  • 完整备份和日志文件备份的频率Full and log file backup frequency

若要还原数据库,必须在存储帐户中找到所需的备份文件,并使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 命令对 SQL VM 执行还原。To restore a database, you must locate the required backup file(s) in the storage account and perform a restore on your SQL VM using SQL Server Management Studio (SSMS) or Transact-SQL commands.

有关如何为 SQL VM 配置自动备份的详细信息,请参阅以下文章之一:For more information on how to configure Automated Backup for SQL VMs, see one of the following articles:

适用于 SQL VM 的 Azure 备份Azure Backup for SQL VMs

Azure 备份为 Azure VM 中运行的 SQL Server 提供企业级备份功能。Azure Backup provides an Enterprise class backup capability for SQL Server running in Azure VMs. 在恢复服务保管库中存储和管理所有备份。All backups are stored and managed in a Recovery Services vault. 此解决方案提供许多优势,尤其是针对企业:There are several advantages that this solution provides, especially for Enterprises:

  • 零基础结构备份:无需管理备份服务器或存储位置。Zero-infrastructure backup: You do not have to manage backup servers or storage locations.
  • 缩放:保护大量的 SQL VM 和数千个数据库。Scale: Protect many SQL VMs and thousands of databases.
  • 标准预付费套餐:此功能是 Azure 备份提供的独立服务,但与所有 Azure 服务,你只需为使用的功能付费。Standard Pay-in-Advance Offer: This capability is a separate service provided by Azure Backup, but as with all Azure services, you only pay for what you use.
  • 集中式管理和监视:通过 Azure 中的单个仪表板集中管理所有备份,包括 Azure 备份支持的其他工作负荷。Central management and monitoring: Centrally manage all of your backups, including other workloads that Azure Backup supports, from a single dashboard in Azure.
  • 策略驱动的备份和保留:为定期备份创建标准备份策略。Policy driven backup and retention: Create standard backup policies for regular backups. 建立保留策略,将备份保留数年之久。Establish retention policies to maintain backups for years.
  • 支持 SQL Always On:检测和保护 SQL Server Always On 配置,并遵循备份可用性组的备份首选项。Support for SQL Always On: Detect and protect a SQL Server Always On configuration and honor the backup Availability Group backup preference.
  • 15 分钟恢复点目标 (RPO) :最多可将 SQL 事务日志备份频率配置为每隔 15 分钟备份一次。15-minute Recovery Point Objective (RPO): Configure SQL transaction log backups up to every 15 minutes.
  • 时间点还原:使用门户将数据库恢复到特定的时间点,无需手动还原多个完整备份、差异备份和日志备份。Point in time restore: Use the portal to recover databases to a specific point in time without having to manually restore multiple full, differential, and log backups.
  • 合并的故障电子邮件警报:针对任何故障配置合并的电子邮件通知。Consolidated email alerts for failures: Configure consolidated email notifications for any failures.
  • 基于角色的访问控制:确定谁可以通过门户管理备份和还原操作。Role-based access control: Determine who can manage backup and restore operations through the portal.

这种适用于 SQL VM 的 Azure 备份解决方案已正式发布。This Azure Backup solution for SQL VMs is generally available.

手动备份Manual backup

若要手动管理 SQL VM 上的备份和还原操作,可以根据所用的 SQL Server 版本使用多个选项。If you want to manually manage backup and restore operations on your SQL VMs, there are several options depending on the version of SQL Server you are using. 有关备份和还原的概述,请根据所用的 SQL Server 版本参阅以下文章之一:For an overview of backup and restore, see one of the following articles based on your version of SQL Server:

以下部分更详细地介绍多个手动备份和还原选项。The following sections describe several manual backup and restore options in more detail.

备份到附加的磁盘Backup to attached disks

对于 Azure VM 中运行的 SQL Server,可以使用 VM 上附加的磁盘作为备份文件目标,通过本机备份和还原技术实现此目的。For SQL Server running in Azure VMs, you can use native backup and restore techniques using attached disks on the VM for the destination of the backup files. 不过,你只能根据虚拟机的大小,将有限数量的磁盘附加到 Azure 虚拟机。However, there is a limit to the number of disks you can attach to an Azure virtual machine, based on the size of the virtual machine. 磁盘管理开销也是一个考虑因素。There is also the overhead of disk management to consider.

有关如何使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 手动创建完整数据库备份的示例,请参阅创建完整数据库备份For an example of how to manually create a full database backup using SQL Server Management Studio (SSMS) or Transact-SQL, see Create a Full Database Backup.

备份到 URLBackup to URL

从 SQL Server 2012 SP1 CU2 开始,可以直接备份和还原到 Azure Blob 存储,此过程也称为备份到 URL。Beginning with SQL Server 2012 SP1 CU2, you can back up and restore directly to Azure Blob storage, which is also known as backup to URL. SQL Server 2016 还对此功能做出了以下增强:SQL Server 2016 also introduced the following enhancements for this feature:

2016 增强功能2016 Enhancement 详细信息Details
条带化Striping 当备份到 Azure Blob 存储时,SQL Server 2016 支持备份到多个 Blob,以便能够备份高达 12.8 TB 的大型数据库。When backing up to Azure blob storage, SQL Server 2016 supports backing up to multiple blobs to enable backing up large databases, up to a maximum of 12.8 TB.
快照备份Snapshot Backup 通过使用 Azure 快照,SQL Server 文件快照备份为使用 Azure Blob 存储服务存储的数据库文件提供近实时备份和快速还原。Through the use of Azure snapshots, SQL Server File-Snapshot Backup provides nearly instantaneous backups and rapid restores for database files stored using the Azure Blob storage service. 使用此功能可简化备份和还原策略。This capability enables you to simplify your backup and restore policies. 文件快照备份还支持时间点还原。File-snapshot backup also supports point in time restore. 有关详细信息,请参阅 Azure 中针对数据库文件的快照备份For more information, see Snapshot Backups for Database Files in Azure.

有关详细信息,请根据所用的 SQL Server 版本参阅以下文章之一:For more information, see the one of the following articles based on your version of SQL Server:

托管备份Managed Backup

从 SQL Server 2014 开始,托管备份会自动在 Azure 存储中创建备份。Beginning with SQL Server 2014, Managed Backup automates the creation of backups to Azure storage. 在幕后,托管备份使用本文上一部分所述的“备份到 URL”功能。Behind the scenes, Managed Backup makes use of the Backup to URL feature described in the previous section of this article. 托管备份也是支持 SQL Server VM 自动备份服务的基础功能。Managed Backup is also the underlying feature that supports the SQL Server VM Automated Backup service.

从 SQL Server 2016 开始,托管备份添加了更多的选项用于配置计划、系统数据库备份,以及完整备份和日志备份的频率。Beginning in SQL Server 2016, Managed backup got additional options for scheduling, system database backup, and full and log backup frequency.

有关详细信息,请根据所用的 SQL Server 版本参阅以下文章之一:For more information, see one of the following articles based on your version of SQL Server:

决策矩阵Decision matrix

下表汇总了 Azure 中 SQL Server 虚拟机的每个备份和还原选项的功能。The following table summarizes the capabilities of each backup and restore option for SQL Server virtual machines in Azure.

自动备份Automated Backup 适用于 SQL 的 Azure 备份Azure Backup for SQL 手动备份Manual backup
需要额外的 Azure 服务Requires additional Azure service 是
在 Azure 门户中配置备份策略Configure backup policy in Azure portal 是 是
在 Azure 门户中还原数据库Restore databases in Azure portal 是
在一个仪表板中管理多个服务器Manage multiple servers in one dashboard 是
时间点还原Point-in-time restore 是 是 是
15 分钟恢复点目标 (RPO)15-minute Recovery Point Objective (RPO) 是 是 是
短期备份保留策略(天)Short-term backup retention policy (days) 是 是
长期备份保留策略(月、年)Long-term backup retention policy (months, years) 是
对 SQL Server Always On 的内置支持Built-in support for SQL Server Always On 是
备份到 Azure 存储帐户Backup to Azure Storage account(s) 是(自动)(automatic) 是(自动)(automatic) 是(由客户管理)(customer managed)
存储和备份文件的管理Management of storage and backup files 是
备份到 VM 上附加的磁盘Backup to attached disks on the VM 是
集中式可自定义备份报告Central customizable backup reports 是
合并的故障电子邮件警报Consolidated email alerts for failures 是
使用 SSMS 或 Transact-SQL 脚本监视备份作业Monitor backup jobs with SSMS or Transact-SQL scripts 是 是 是
使用 SSMS 或 Transact-SQL 脚本还原数据库Restore databases with SSMS or Transact-SQL scripts 是 是

后续步骤Next steps

规划 Azure VM 中的 SQL Server 部署时,可在以下指南中找到预配指导:如何在 Azure 门户中预配 Windows SQL Server 虚拟机If you are planning your deployment of SQL Server in an Azure VM, you can find provisioning guidance in the following guide: How to provision a Windows SQL Server virtual machine in the Azure portal.

尽管备份和还原可用于迁移数据,但是,Azure VM 上的 SQL Server 可能还存在更便捷的数据迁移路径。Although backup and restore can be used to migrate your data, there are potentially easier data migration paths to SQL Server on an Azure VM. 有关迁移选项和建议的完整讨论,请参阅将数据库迁移到 Azure VM 上的 SQL ServerFor a full discussion of migration options and recommendations, see Migrating a Database to SQL Server on an Azure VM.