将 Azure SQL 托管实例中的数据库还原到之前的某个时间点Restore a database in Azure SQL Managed Instance to a previous point in time

适用于: Azure SQL 托管实例

使用时间点还原 (PITR),可以创建一个数据库作为另一个数据库在过去某个时间点的副本。Use point-in-time restore (PITR) to create a database as a copy of another database from some time in the past. 本文介绍如何对 Azure SQL 托管实例中的数据库执行时间点还原。This article describes how to do a point-in-time restore of a database in Azure SQL Managed Instance.

可以在恢复方案中使用时间点还原,例如,解决错误导致的事件、错误加载了数据或删除了重要数据等问题。Point-in-time restore is useful in recovery scenarios, such as incidents caused by errors, incorrectly loaded data, or deletion of crucial data. 还可以使用时间点还原进行测试或审核。You can also use it simply for testing or auditing. 备份文件保留 7 到 35 天,具体取决于数据库设置。Backup files are kept for 7 to 35 days, depending on your database settings.

时间点还原可以通过以下方式还原数据库:Point-in-time restore can restore a database:

  • 从现有数据库还原。from an existing database.
  • 从已删除的数据库还原。from a deleted database.
  • 还原到同一个或其他 SQL 托管实例。to the same SQL Managed Instance, or to another SQL Managed Instance.

限制Limitations

SQL 托管实例的时间点还原具有以下限制:Point-in-time restore to SQL Managed Instance has the following limitations:

  • 从一个 SQL 托管实例还原到另一个实例时,这两个实例必须在同一订阅和区域中。When you're restoring from one instance of SQL Managed Instance to another, both instances must be in the same subscription and region. 目前不支持跨区域和跨订阅的还原。Cross-region and cross-subscription restore aren't currently supported.
  • 无法对整个 SQL 托管实例执行时间点还原。Point-in-time restore of a whole SQL Managed Instance is not possible. 本文仅介绍可行的操作:对 SQL 托管实例上托管的数据库执行时间点还原。This article explains only what's possible: point-in-time restore of a database that's hosted on SQL Managed Instance.

警告

请注意 SQL 托管实例的存储大小。Be aware of the storage size of your SQL Managed Instance. 根据要还原数据的大小,可能会耗尽实例存储。Depending on size of the data to be restored, you might run out of instance storage. 如果没有足够的空间用于存储还原的数据,请使用其他方法。If there isn't enough space for the restored data, use a different approach.

下表显示了 SQL 托管实例的时间点还原方案:The following table shows point-in-time restore scenarios for SQL Managed Instance:

将现有的数据库还原到 SQL 托管实例的同一实例Restore existing DB to the same instance of SQL Managed Instance 将现有的数据库还原到另一个 SQL 托管实例Restore existing DB to another SQL Managed Instance 将已删除的数据库还原到同一个 SQL 托管实例Restore dropped DB to same SQL Managed Instance 将已删除的数据库还原到另一个 SQL 托管实例Restore dropped DB to another SQL Managed Instance
Azure 门户Azure portal Yes No Yes No
Azure CLIAzure CLI Yes Yes No No
PowerShellPowerShell Yes Yes Yes Yes

还原现有数据库Restore an existing database

使用 Azure 门户、Powershell 或 Azure CLI 将现有数据库还原到同一个 SQL 托管实例。Restore an existing database to the same SQL Managed Instance using the Azure portal, PowerShell, or the Azure CLI. 若要将数据库还原到另一个 SQL 实例,请使用 PowerShell 或 Azure CLI,这样可指定目标 SQL 托管实例和资源组的属性。To restore a database to another SQL Managed Instance, use PowerShell or the Azure CLI so you can specify the properties for the target SQL Managed Instance and resource group. 如果不指定这些参数,数据库将默认还原到现有 SQL 托管实例。If you don't specify these parameters, the database will be restored to the existing SQL Managed Instance by default. 目前不支持通过 Azure 门户还原到另一个 SQL 托管实例。The Azure portal doesn't currently support restoring to another SQL Managed Instance.

  1. 登录到 Azure 门户Sign into the Azure portal.

  2. 转到你的 SQL 托管实例,选择要还原的数据库。Go to your SQL Managed Instance and select the database that you want to restore.

  3. 在数据库页上选择“还原”:Select Restore on the database page:

    使用 Azure 门户还原数据库

  4. 在“还原”页上,选择要将数据库还原到的日期时间点。On the Restore page, select the point for the date and time that you want to restore the database to.

  5. 选择“确认”以还原数据库。Select Confirm to restore your database. 此操作会启动还原过程,期间会创建一个新数据库,并在其中填充原始数据库在指定时间点的数据。This action starts the restore process, which creates a new database and populates it with data from the original database at the specified point in time. 有关恢复过程的详细信息,请参阅恢复时间For more information about the recovery process, see Recovery time.

还原已删除的数据库Restore a deleted database

可以使用 PowerShell 或 Azure 门户还原已删除的数据库。Restoring a deleted database can be done by using PowerShell or Azure portal. 若要将已删除的数据库还原到同一个实例,请使用 Azure 门户或 PowerShell。To restore a deleted database to the same instance, use either the Azure portal or PowerShell. 若要将已删除的数据库还原到另一个实例,请使用 PowerShell。To restore a deleted database to another instance, use PowerShell.

门户Portal

若要使用 Azure 门户恢复托管数据库,请打开 SQL 托管实例概述页面,然后选择“删除的数据库”。To recover a managed database using the Azure portal, open the SQL Managed Instance overview page, and select Deleted databases. 选择要还原的已删除数据库,然后键入要使用从备份还原的数据创建的新数据库的名称。Choose 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 实例数据库的屏幕截图

../../sql-database../../sql-database

PowerShellPowerShell

若要将数据库还原到同一个实例,请更新参数值,然后运行以下 PowerShell 命令:To restore a database to the same instance, update the parameter values and then run the following PowerShell command:

$subscriptionId = "<Subscription ID>"
Get-AzSubscription -SubscriptionId $subscriptionId
Select-AzSubscription -SubscriptionId $subscriptionId

$resourceGroupName = "<Resource group name>"
$managedInstanceName = "<SQL Managed Instance name>"
$deletedDatabaseName = "<Source database name>"
$targetDatabaseName = "<target database name>"

$deletedDatabase = Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $resourceGroupName `
-InstanceName $managedInstanceName -DatabaseName $deletedDatabaseName

Restore-AzSqlinstanceDatabase -Name $deletedDatabase.Name `
   -InstanceName $deletedDatabase.ManagedInstanceName `
   -ResourceGroupName $deletedDatabase.ResourceGroupName `
   -DeletionDate $deletedDatabase.DeletionDate `
   -PointInTime UTCDateTime `
   -TargetInstanceDatabaseName $targetDatabaseName

若要将数据库还原到另一个 SQL 托管实例,还需指定目标资源组和目标 SQL 托管实例的名称:To restore the database to another SQL Managed Instance, also specify the names of the target resource group and target SQL Managed Instance:

$targetResourceGroupName = "<Resource group of target SQL Managed Instance>"
$targetInstanceName = "<Target SQL Managed Instance name>"

Restore-AzSqlinstanceDatabase -Name $deletedDatabase.Name `
   -InstanceName $deletedDatabase.ManagedInstanceName `
   -ResourceGroupName $deletedDatabase.ResourceGroupName `
   -DeletionDate $deletedDatabase.DeletionDate `
   -PointInTime UTCDateTime `
   -TargetInstanceDatabaseName $targetDatabaseName `
   -TargetResourceGroupName $targetResourceGroupName `
   -TargetInstanceName $targetInstanceName 

覆盖现有数据库Overwrite an existing database

若要覆盖现有数据库,必须执行以下操作:To overwrite an existing database, you must:

  1. 删除要覆盖的现有数据库。Drop the existing database that you want to overwrite.
  2. 将时间点还原的数据库重命名为已删除的数据库的名称。Rename the point-in-time-restored database to the name of the database that you dropped.

删除原始数据库Drop the original database

可以使用 Azure 门户、PowerShell 或 Azure CLI 删除数据库。You can drop the database by using the Azure portal, PowerShell, or the Azure CLI.

还可通过直接连接到 SQL 托管实例、启动 SQL Server Management Studio (SSMS),然后运行以下 Transact-SQL (T-SQL) 命令来删除数据库:You can also drop the database by connecting to the SQL Managed Instance directly, starting SQL Server Management Studio (SSMS), and then running the following Transact-SQL (T-SQL) command:

DROP DATABASE WorldWideImporters;

使用以下方法之一连接到 SQL 托管实例中的数据库:Use one of the following methods to connect to your database in the SQL Managed Instance:

在 Azure 门户上,选择 SQL 托管实例中的数据库,然后选择“删除”。In the Azure portal, select the database from the SQL Managed Instance, and then select Delete.

使用 Azure 门户删除数据库

更改新数据库名称,使之与原始数据库名称匹配Alter the new database name to match the original database name

直接连接到 SQL 托管实例并启动 SQL Server Management Studio。Connect directly to the SQL Managed Instance and start SQL Server Management Studio. 然后运行以下 Transact-SQL (T-SQL) 查询。Then, run the following Transact-SQL (T-SQL) query. 该查询将已还原数据库的名称更改为要覆盖的已删除数据库的名称。The query will change the name of the restored database to that of the dropped database that you intend to overwrite.

ALTER DATABASE WorldWideImportersPITR MODIFY NAME = WorldWideImporters;

使用以下方法之一连接到 SQL 托管实例中的数据库:Use one of the following methods to connect to your database in SQL Managed Instance:

后续步骤Next steps

了解自动备份Learn about automated backups.