使用自动数据库备份进行恢复 - Azure SQL 托管实例和 SQL 托管实例Recover using automated database backups - Azure SQL Database & SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

以下选项适用于使用自动数据库备份的数据库恢复:The following options are available for database recovery by using automated database backups. 方法:You can:

  • 在恢复到保持期内指定时间点的同一服务器上创建新数据库。Create a new database on the same server, recovered to a specified point in time within the retention period.
  • 在恢复到已删除数据库的删除时间的同一服务器上创建数据库。Create a database on the same server, recovered to the deletion time for a deleted database.
  • 在恢复到最新备份点的相同区域中的任一服务器上创建新数据库。Create a new database on any server in the same region, recovered to the point of the most recent backups.
  • 在恢复到最近复制的备份点的其他任何区域中的任一服务器上创建新数据库。Create a new database on any server in any other region, recovered to the point of the most recent replicated backups.

如果你已配置备份长期保留,还可以从任一服务器上的任何长期保留备份创建新数据库。If you configured backup long-term retention, you can also create a new database from any long-term retention backup on any server.

重要

还原期间无法覆盖现有数据库。You can't overwrite an existing database during restore.

默认情况下,Azure SQL 数据库和 Azure SQL 托管实例备份存储在异地复制的 Blob 存储(RA-GRS 存储类型)中。By default, Azure SQL Database and Azure SQL Managed Instance backups are stored in geo-replicated blob storage (RA-GRS storage type). 此外,SQL 托管实例支持本地冗余 (LRS) 备份存储。In addition, SQL Managed Instance supports locally-redundant (LRS) backup storage. 冗余可确保数据免受计划内和计划外事件的影响,包括暂时性的硬件故障、网络中断或断电、大范围自然灾害等。Redundancy ensures that your data is protected from planned and unplanned events, including transient hardware failures, network or power outages, and massive natural disasters.

重要

为备份配置存储冗余仅适用于托管实例,允许在创建过程中进行配置。Configuring storage redundancy for backups is available for managed instance only and allowed during create process. 预配资源后,不能更改备份存储冗余选项。Once the resource is provisioned, you cannot change the backup storage redundancy option.

使用“标准”或“高级”服务层级时,数据库还原可能会产生额外的存储费用。When you're using the Standard or Premium service tier, your database restore might incur an extra storage cost. 如果还原的数据库的最大大小大于目标数据库的服务层级和性能级别包含的存储量,则会产生额外的费用。The extra cost is incurred when the maximum size of the restored database is greater than the amount of storage included with the target database's service tier and performance level. 有关额外存储定价的详细信息,请参阅 SQL 数据库定价页面For pricing details of extra storage, see the SQL Database pricing page. 如果实际使用的空间量小于附送的存储量,可以通过将数据库最大大小设置为附送的量,来避免产生额外的费用。If the actual amount of used space is less than the amount of storage included, you can avoid this extra cost by setting the maximum database size to the included amount.

恢复时间Recovery time

使用自动数据库备份还原数据库的恢复时间受几个因素的影响:The recovery time to restore a database by using automated database backups is affected by several factors:

  • 数据库的大小。The size of the database.
  • 数据库的计算大小。The compute size of the database.
  • 所涉及的事务日志数。The number of transaction logs involved.
  • 需要重新播放以恢复到还原点的活动数量。The amount of activity that needs to be replayed to recover to the restore point.
  • 还原到不同区域时的网络带宽。The network bandwidth if the restore is to a different region.
  • 目标区域中正在处理的并行还原请求数。The number of concurrent restore requests being processed in the target region.

对于较大或非常活跃的数据库,还原可能要花费几个小时。For a large or very active database, the restore might take several hours. 如果某个区域出现长时间的服务中断,则灾难恢复可能会发起大量的异地还原请求。If there is a prolonged outage in a region, it's possible that a high number of geo-restore requests will be initiated for disaster recovery. 存在很多请求时,单个数据库的恢复时间可能会增加。When there are many requests, the recovery time for individual databases can increase. 大部分数据库还原操作可在 12 小时内完成。Most database restores finish in less than 12 hours.

对于单个订阅,并发还原请求的数目存在限制。For a single subscription, there are limitations on the number of concurrent restore requests. 这些限制适用于时间点还原、异地还原和从长期保留备份中还原的任意组合。These limitations apply to any combination of point-in-time restores, geo-restores, and restores from long-term retention backup.

部署选项Deployment option 处理的并发请求数最多为 #Max # of concurrent requests being processed 提交的并发请求数最多为 #Max # of concurrent requests being submitted
单个数据库(每个订阅)Single database (per subscription) 1010 6060
弹性池(每个池)Elastic pool (per pool) 44 200200

没有任何内置的方法可以还原整个服务器。There isn't a built-in method to restore the entire server. 有关如何完成此任务的示例,请参阅 Azure SQL 数据库:完整服务器恢复For an example of how to accomplish this task, see Azure SQL Database: Full server recovery.

重要

若要使用自动备份进行恢复,你必须是订阅中 SQL Server 参与者角色或 SQL 托管实例参与者角色(具体取决于恢复目标)的成员,或者你必须是订阅所有者。To recover by using automated backups, you must be a member of the SQL Server Contributor role or SQL Managed Instance Contributor role (depending on the recovery destination) in the subscription, or you must be the subscription owner. 有关详细信息,请参阅 RBAC:内置角色For more information, see RBAC: Built-in roles. 可以使用 Azure 门户、PowerShell 或 REST API 进行恢复。You can recover by using the Azure portal, PowerShell, or the REST API. 不能使用 Transact-SQL。You can't use Transact-SQL.

时间点还原Point-in-time restore

可以使用 Azure 门户、PowerShellREST API,将独立数据库、共用数据库或实例数据库还原到早期的时间点。You can restore a standalone, pooled, or instance database to an earlier point in time by using the Azure portal, PowerShell, or the REST API. 该请求可以指定还原的数据库的任何服务层级或计算大小。The request can specify any service tier or compute size for the restored database. 确保要将数据库还原到其中的服务器上有足够的资源。Ensure that you have sufficient resources on the server to which you are restoring the database.

完成后,还原过程将在原始数据库所在的同一台服务器上创建新数据库。When complete, the restore creates a new database on the same server as the original database. 还原的数据库将基于其服务层级和计算大小按标准费率计费。The restored database is charged at normal rates, based on its service tier and compute size. 在数据库还原完成之前,不会产生费用。You don't incur charges until the database restore is complete.

通常,为恢复目的将数据库还原到较早点。You generally restore a database to an earlier point for recovery purposes. 可将还原的数据库视为原始数据库的替代数据库,或使用它作为数据源来更新原始数据库。You can treat the restored database as a replacement for the original database or use it as a data source to update the original database.

  • 数据库替换Database replacement

    如果你打算用还原的数据库替换原始数据库,则应该指定原始数据库的计算大小和服务层级。If you intend the restored database to be a replacement for the original database, you should specify the original database's compute size and service tier. 然后,可以使用 T-SQL 中的 ALTER DATABASE 命令来重命名原始数据库,并为还原的数据库指定原有的名称。You can then rename the original database, and give the restored database the original name by using the ALTER DATABASE command in T-SQL.

  • 数据恢复Data recovery

    如果你打算从还原的数据库检索数据以从用户或应用程序错误中恢复,则需要编写并执行一个数据恢复脚本,用于从还原的数据库提取数据并将其应用到原始数据库。If you plan to retrieve data from the restored database to recover from a user or application error, you need to write and execute a data recovery script that extracts data from the restored database and applies to the original database. 尽管还原操作可能需要很长时间才能完成,但整个还原过程中,都可在数据库列表中看到还原数据库。Although the restore operation may take a long time to complete, the restoring database is visible in the database list throughout the restore process. 如果在还原期间删除数据库,将取消还原操作,并且不会针对未完成还原的数据库向你收费。If you delete the database during the restore, the restore operation will be canceled and you will not be charged for the database that did not complete the restore.

使用 Azure 门户进行时间点还原Point-in-time restore by using Azure portal

可以在 Azure 门户中通过要还原的数据库的概述边栏选项卡,将单一数据库或实例数据库恢复到某个时间点。You can recover a single or instance database to a point in time from the overview blade of the database you want to restore in the Azure portal.

SQL 数据库SQL Database

若要使用 Azure 门户将数据库恢复到某个时间点,请打开该数据库的概述页,并在工具栏上选择“还原”。To recover a database to a point in time by using the Azure portal, open the database overview page and select Restore on the toolbar. 选择备份源,然后选择要从中创建新数据库的时间点备份点。Choose the backup source, and select the point-in-time backup point from which a new database will be created.

数据库还原选项的屏幕截图

SQL 托管实例SQL Managed Instance

若要使用 Azure 门户将托管实例数据库恢复到某个时间点,请打开该数据库的概述页,并在工具栏上选择“还原”。To recover a managed instance database to a point in time by using the Azure portal, open the database overview page, and select Restore on the toolbar. 选择要从中创建新数据库的时间点备份点。Choose the point-in-time backup point from which a new database will be created.

数据库还原选项的屏幕截图

提示

若要以编程方式从备份还原数据库,请参阅使用自动备份以编程方式执行恢复To programmatically restore a database from a backup, see Programmatically performing recovery using automated backups.

已删除的数据库还原Deleted database restore

可将已删除的数据库还原到同一服务器或同一托管实例上的删除时间或早期的时间点。You can restore a deleted database to the deletion time, or an earlier point in time, on the same server or the same managed instance. 可以通过 Azure 门户、PowerShellREST (createMode=Restore) 完成此操作。You can accomplish this through the Azure portal, PowerShell, or the REST (createMode=Restore). 通过从备份创建新数据库来还原已删除的数据库。You restore a deleted database by creating a new database from the backup.

重要

如果删除服务器或托管实例,其所有数据库也会一并删除,并且无法恢复。If you delete a server or managed instance, all its databases are also deleted and can't be recovered. 无法还原已删除的服务器或托管实例。You can't restore a deleted server or managed instance.

使用 Azure 门户还原已删除的数据库Deleted database restore by using the Azure portal

通过 Azure 门户还原在服务器或托管实例资源中删除的数据库。You restore deleted databases from the Azure portal from the server or managed instance resource.

SQL 数据库SQL Database

若要使用 Azure 门户将已删除的数据库恢复到删除时间,请打开服务器概述页,然后选择“删除的数据库”。To recover a deleted database to the deletion time by using the Azure portal, open the server overview page, and select Deleted databases. 选择要还原的已删除数据库,然后键入要使用从备份还原的数据创建的新数据库的名称。Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.

还原已删除的数据库的屏幕截图

SQL 托管实例SQL Managed Instance

若要使用 Azure 门户恢复托管数据库,请打开托管实例概览页,然后选择“删除的数据库”。To recover a managed database by using the Azure portal, open the managed instance overview page, and select Deleted databases. 选择要还原的已删除数据库,然后键入要使用从备份还原的数据创建的新数据库的名称。Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.

还原已删除的 Azure SQL 托管实例数据库的屏幕截图

使用 PowerShell 还原已删除的数据库Deleted database restore by using PowerShell

在 PowerShell 中使用以下示例脚本还原 SQL 数据库或 SQL 托管实例的已删除数据库。Use the following sample scripts to restore a deleted database for either SQL Database or SQL Managed Instance by using PowerShell.

SQL 数据库SQL Database

有关演示如何在 Azure SQL 数据库中还原已删除的数据库的示例 PowerShell 脚本,请参阅使用 PowerShell 还原数据库For a sample PowerShell script showing how to restore a deleted database in Azure SQL Database, see Restore a database using PowerShell.

SQL 托管实例SQL Managed Instance

有关演示如何还原已删除的实例数据库的示例 PowerShell 脚本,请参阅使用 PowerShell 还原已删除的实例数据库For a sample PowerShell script showing how to restore a deleted instance database, see Restore deleted instance database using PowerShell

提示

若要以编程方式还原已删除的数据库,请参阅使用自动备份以编程方式执行恢复To programmatically restore a deleted database, see Programmatically performing recovery using automated backups.

异地还原Geo-restore

重要

异地还原仅适用于配置了异地冗余 (RA-GRS) 备份存储类型的托管实例。Geo-restore is only available for managed instances configured with geo-redundant (RA-GRS) backup storage type. 配置有本地冗余备份存储类型的托管实例不支持异地还原。Managed instances configured with with locally-redundant backup storage types do not support geo-restore.

你可以在任何 Azure 区域中,从最新的异地复制备份还原任何 SQL 数据库服务器上的数据库或任何托管实例上的实例数据库。You can restore a database on any SQL Database server or an instance database on any managed instance in any Azure region from the most recent geo-replicated backups. 异地还原使用异地复制的备份作为源。Geo-restore uses a geo-replicated backup as its source. 即使由于服务中断而无法访问数据库或数据中心,也依然能够请求异地还原。You can request geo-restore even if the database or datacenter is inaccessible due to an outage.

当数据库因其托管区域发生事故而不可用时,异地还原是默认的恢复选项。Geo-restore is the default recovery option when your database is unavailable because of an incident in the hosting region. 可将数据库还原到任何其他区域中的服务器。You can restore the database to a server in any other region. 创建备份后,将其异地复制到其他区域中的 Azure Blob 时会出现延迟。There is a delay between when a backup is taken and when it is geo-replicated to an Azure blob in a different region. 因此,还原的数据库可能比原始数据库晚最多一个小时。As a result, the restored database can be up to one hour behind the original database. 下图演示了如何从另一区域中的最后一个可用备份还原数据库。The following illustration shows a database restore from the last available backup in another region.

异地还原插图

使用 Azure 门户进行异地还原Geo-restore by using the Azure portal

在 Azure 门户中,创建一个新的单一数据库或托管实例数据库,并选择可用的异地还原备份。From the Azure portal, you create a new single or managed instance database and select an available geo-restore backup. 新建的数据库包含异地还原的备份数据。The newly created database contains the geo-restored backup data.

SQL 数据库SQL Database

若要通过 Azure 门户异地还原所选区域和服务器中的单一数据库,请执行以下步骤:To geo-restore a single database from the Azure portal in the region and server of your choice, follow these steps:

  1. 在“仪表板”中,选择“添加” > “创建 SQL 数据库”。 From Dashboard, select Add > Create SQL Database. 在“基本信息”选项卡上输入所需的信息。On the Basics tab, enter the required information.

  2. 选择“其他设置”。Select Additional settings.

  3. 对于“使用现有数据”,请选择“备份”。 For Use existing data, select Backup.

  4. 对于“备份”,请从可用的异地还原备份列表中选择一个备份。For Backup, select a backup from the list of available geo-restore backups.

    “创建 SQL 数据库”选项的屏幕截图

完成从备份创建新数据库的过程。Complete the process of creating a new database from the backup. 在 Azure SQL 数据库中创建数据库时,该数据库包含已还原的异地还原备份。When you create a database in Azure SQL Database, it contains the restored geo-restore backup.

SQL 托管实例SQL Managed Instance

若要通过 Azure 门户将托管实例数据库异地还原到所选区域中的现有托管实例,请选择要在其上还原数据库的托管实例。To geo-restore a managed instance database from the Azure portal to an existing managed instance in a region of your choice, select a managed instance on which you want a database to be restored. 执行以下步骤:Follow these steps:

  1. 选择“新建数据库”。Select New database.

  2. 键入所需的数据库名称。Type a desired database name.

  3. 在“使用现有数据”下,选择“备份”。 Under Use existing data, select Backup.

  4. 从可用的异地还原备份列表中选择一个备份。Select a backup from the list of available geo-restore backups.

    “新建数据库”选项的屏幕截图

完成创建新数据库的过程。Complete the process of creating a new database. 创建实例数据库时,该数据库包含已还原的异地还原备份。When you create the instance database, it contains the restored geo-restore backup.

使用 PowerShell 进行异地还原Geo-restore by using PowerShell

SQL 数据库SQL Database

有关演示如何对单一数据库执行异地还原的 PowerShell 脚本,请参阅使用 PowerShell 将单一数据库还原到早期的时间点For a PowerShell script that shows how to perform geo-restore for a single database, see Use PowerShell to restore a single database to an earlier point in time.

SQL 托管实例SQL Managed Instance

有关演示如何对托管实例数据库执行异地还原的 PowerShell 脚本,请参阅使用 PowerShell 将托管实例数据库还原到另一个地理区域For a PowerShell script that shows how to perform geo-restore for a managed instance database, see Use PowerShell to restore a managed instance database to another geo-region.

异地还原注意事项Geo-restore considerations

不能对异地辅助数据库执行时间点还原。You can't perform a point-in-time restore on a geo-secondary database. 只能对主数据库执行此操作。You can do so only on a primary database. 有关使用异地还原在中断后恢复的详细信息,请参阅在中断后恢复For detailed information about using geo-restore to recover from an outage, see Recover from an outage.

重要

异地还原是 SQL 数据库和 SQL 托管实例中提供的最基本的灾难恢复解决方案。Geo-restore is the most basic disaster-recovery solution available in SQL Database and SQL Managed Instance. 它依赖于自动创建的异地复制备份,其恢复点目标 (RPO) 为 1 小时,估计恢复时间最长为 12 小时。It relies on automatically created geo-replicated backups with a recovery point objective (RPO) equal to 1 hour and an estimated recovery time of up to 12 hours. 它不保证在发生区域性的服务中断后,目标区域可提供足够的容量来还原数据库,因为此时的需求可能会急剧上升。It doesn't guarantee that the target region will have the capacity to restore your databases after a regional outage, because a sharp increase of demand is likely. 如果应用程序使用相对较小的数据库并且不是业务关键型应用程序,则异地还原是适当的灾难恢复解决方案。If your application uses relatively small databases and is not critical to the business, geo-restore is an appropriate disaster-recovery solution.

对于需要大型数据库且必须确保业务连续性的业务关键型应用程序,请使用自动故障转移组For business-critical applications that require large databases and must ensure business continuity, use Auto-failover groups. 此功能提供的 RPO 和恢复时间目标要低得多,并且始终可以保证容量。It offers a much lower RPO and recovery time objective, and the capacity is always guaranteed.

有关业务连续性选项的详细信息,请参阅业务连续性概述For more information about business continuity choices, see Overview of business continuity.

使用自动备份进行编程恢复Programmatic recovery using automated backups

也可以使用 Azure PowerShell 或 REST API 进行恢复。You can also use Azure PowerShell or the REST API for recovery. 下表描述了可用的命令集。The following tables describe the set of commands available.

PowerShellPowerShell

备注

本文进行了更新,以便使用新的 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.

重要

PowerShell Azure 资源管理器模块仍受 SQL 数据库和 SQL 托管实例的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by SQL Database and SQL Managed Instance, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 Azure 资源管理器模块中的命令参数大体上是相同的。Arguments for the commands in the Az module and in Azure Resource Manager modules are to a great extent identical.

SQL 数据库SQL Database

若要还原独立数据库或共用数据库,请参阅 Restore-AzSqlDatabaseTo restore a standalone or pooled database, see Restore-AzSqlDatabase.

CmdletCmdlet 说明Description
Get-AzSqlDatabaseGet-AzSqlDatabase 获取一个或多个数据库。Gets one or more databases.
Get-AzSqlDeletedDatabaseBackupGet-AzSqlDeletedDatabaseBackup 获取可以还原的已删除数据库。Gets a deleted database that you can restore.
Get-AzSqlDatabaseGeoBackupGet-AzSqlDatabaseGeoBackup 获取数据库的异地冗余备份。Gets a geo-redundant backup of a database.
Restore-AzSqlDatabaseRestore-AzSqlDatabase 还原数据库。Restores a database.

提示

有关演示如何对数据库执行时间点还原的示例 PowerShell 脚本,请参阅通过使用 PowerShell 还原数据库For a sample PowerShell script that shows how to perform a point-in-time restore of a database, see Restore a database by using PowerShell.

SQL 托管实例SQL Managed Instance

若要还原托管实例数据库,请参阅 Restore-AzSqlInstanceDatabaseTo restore a managed instance database, see Restore-AzSqlInstanceDatabase.

CmdletCmdlet 说明Description
Get-AzSqlInstanceGet-AzSqlInstance 获取一个或多个托管实例。Gets one or more managed instances.
Get-AzSqlInstanceDatabaseGet-AzSqlInstanceDatabase 获取实例数据库。Gets an instance database.
Restore-AzSqlInstanceDatabaseRestore-AzSqlInstanceDatabase 还原实例数据库。Restores an instance database.

REST APIREST API

使用 REST API 还原数据库:To restore a database by using the REST API:

APIAPI 说明Description
REST (createMode=Recovery)REST (createMode=Recovery) 还原数据库。Restores a database.
获取创建或更新数据库状态Get Create or Update Database Status 在还原操作过程中返回状态。Returns the status during a restore operation.

Azure CLIAzure CLI

SQL 数据库SQL Database

若要使用 Azure CLI 还原单一数据库或共用数据库,请参阅 az sql db restoreTo restore a single or pooled database by using the Azure CLI, see az sql db restore.

SQL 托管实例SQL Managed Instance

若要使用 Azure CLI 还原托管实例数据库,请参阅 az sql midb restoreTo restore a managed instance database by using the Azure CLI, see az sql midb restore

摘要Summary

自动备份可保护数据库,使其免受用户和应用程序错误、意外的数据库删除和长时间中断的影响。Automatic backups protect your databases from user and application errors, accidental database deletion, and prolonged outages. 此内置的功能适用于所有服务层级和计算大小。This built-in capability is available for all service tiers and compute sizes.

后续步骤Next steps