Restore a database in Azure SQL Managed Instance to a previous point in time

Article
03/07/2023
10 minutes to read

Applies to: Azure SQL Managed Instance

You can use point-in-time restore to create a database that's a copy of a database at a specific, earlier point in time. This article describes how to do a point-in-time restore of a database in Azure SQL Managed Instance.

Note

The Create or Update v02.01.2022 has been deprecated. Starting in January 2023, use the replacement Create or Update v5.0.2022 API call for all database restore operations.

Overview

Point-in-time restore is useful in recovery scenarios, like for an incident that's caused by error or failure, when data doesn't load correctly, or if crucial data is deleted. You can also use it simply to test and audit your database deployment. Azure backup files are kept for 7 to 35 days depending on your database settings.

You can use point-in-time restore to restore a database in these scenarios:

From an existing database
From a deleted database
To the same managed instance or to a different managed instance
To a managed instance in the same subscription or to a managed instance in a different subscription

The following table shows point-in-time restore scenarios for SQL Managed Instance:

Scenario	Azure portal	Azure CLI	PowerShell
Restore an existing database to the same managed instance	Yes	Yes	Yes
Restore an existing database to a different managed instance	Yes	Yes	Yes
Restore a deleted database to the same managed instance	Yes	Yes	Yes
Restore a deleted database to a different managed instance	Yes	Yes	Yes
Restore an existing database to a managed instance in another subscription	Yes	Yes	Yes
Restore a deleted database to a managed instance in another subscription	Yes	Yes	Yes

Permissions

To recover a database, you must be either:

A member of the SQL Server Contributor role or SQL Managed Instance Contributor role (depending on the recovery destination) in the subscription
The subscription owner

To restore database to a different target subscription, if you're not in the SQL Managed Instance Contributor role you should also have the following permissions:

Microsoft.Sql/managedInstances/databases/readBackups/action on the source SQL managed instance.
Microsoft.Sql/managedInstances/crossSubscriptionPITR/action on the target SQL managed instance.

For more information, see Azure RBAC: Built-in roles.

Limitations

These limitations apply to point-in-time restore in SQL Managed Instance:

You can't use point-in-time restore to recover an entire SQL Managed Instance deployment. Use point-in-time restore only to make a copy of a database that's hosted on SQL Managed Instance.
Limitations in point-in-time restore depend on whether you're restoring your database to a managed instance in the same subscription or to a managed instance in a different subscription.
When service endpoint policies are enabled on Azure SQL Managed Instance, placing a service endpoint policy on a subnet prevents point-in-time restores from instances in different subnets.

Warning

Be aware of the storage size of your managed instance. Depending on the size of the data to be restored, you might run out of storage for your managed instance. If you don't have enough storage space in your managed instance for the restored data, use a different approach.

Restore to the same subscription

If you restore from one managed instance to another managed instance in the same Azure subscription, both managed instances must be in the same region. Currently, cross-region restore isn't supported.

Restore to a different subscription

Restoring a point-in-time restore backup across subscriptions has the following limitations:

Both subscriptions must be in the same region.
Both subscriptions must be in the same tenant.
The subscription type must be either Enterprise Agreement, Cloud Solution Provider, Microsoft Certified Partner, or pay-as-you-go.
You can use the restore action only on the primary instance.
Geo-replicated backups currently aren't supported for cross-subscription point-in-time restore.
The user who takes the restore action must either have the SQL Managed Instance Contributor role assignment or have these explicit permissions:
- Microsoft.Sql/managedInstances/databases/readBackups/action on the source SQL managed instance.
- Microsoft.Sql/managedInstances/crossSubscriptionPITR/action on the target SQL managed instance.
If you bring your own key (BYOK), the key must be present in both subscriptions.

Restore an existing database

You can restore an existing database in the same subscription by using the Azure portal, PowerShell, or the Azure CLI. If you restore to a different instance in the same subscription by using PowerShell or the Azure CLI, be sure to specify the properties for the target SQL Managed Instance resource. The database is restored to the same instance by default.

If you restore to a different subscription, the Create or Update v5.0.2022 API call that underlies the restore action must contain restorePointInTime, crossSubscriptionTargetManagedInstanceId, and either crossSubscriptionSourceDatabaseId or crossSubscriptionRestorableDroppedDatabaseId.

Sign in to the Azure portal.
In SQL Managed Instance, go to your source managed instance.
In Managed Instance databases, select the database to restore.
In the command bar, select Restore.
In Create Azure SQL Managed Database on the Basics tab:
1. Under Project details, select the target destination subscription and resource group that contain the managed instance to restore the database to.
2. Under Database details, enter a new name for your restored database and the target managed instance to restore the database to.
3. Select the Data source tab.
In Data source:
1. Select Continuous backup. Select the subscription and resource group for the source database to restore.
2. In Managed Database, select the database to restore. Select the point-in-time restore point to restore the database from.
3. Select the Additional settings tab.
In Additional settings:
1. Either select or clear the option for your restored database to inherit the retention settings of the source database. If you don't use this option, select Configure retention to set a new retention policy.
2. Select the Review + create tab to validate your database restore settings.
In Review + create, when validation is successful, select Create 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.

Use Azure PowerShell to restore your database. For more information, review Install the Azure PowerShell module. For more information, see Restore-AzSqlInstanceDatabase.

Run one of the following code options with your values substituted for the parameters.

To restore the database to the same managed instance:

$subscriptionId = "<subscription ID>"
$resourceGroupName = "<resource group name>"
$managedInstanceName = "<managed instance name>"
$databaseName = "<source database>"
$pointInTime = "2018-06-27T08:51:39.3882806Z"
$targetDatabase = "<name of the new database to create>"

Get-AzSubscription -SubscriptionId $subscriptionId
Select-AzSubscription -SubscriptionId $subscriptionId

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
                              -ResourceGroupName $resourceGroupName `
                              -InstanceName $managedInstanceName `
                              -Name $databaseName `
                              -PointInTime $pointInTime `
                              -TargetInstanceDatabaseName $targetDatabase `

To restore the database to another managed instance, also specify the names of the target resource group and the target managed instance:

$targetResourceGroupName = "<resource group of the target managed instance>"
$targetInstanceName = "<name of the target managed instance>"

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
                              -ResourceGroupName $resourceGroupName `
                              -InstanceName $managedInstanceName `
                              -Name $databaseName `
                              -PointInTime $pointInTime `
                              -TargetInstanceDatabaseName $targetDatabase `
                              -TargetResourceGroupName $targetResourceGroupName `
                              -TargetInstanceName $targetInstanceName

To restore the database to another subscription, set the context to the target subscription (Set-AzContext) and be sure to provide a value for the required parameter -TargetSubscriptionID:

Set-AzContext -SubscriptionID "targetSubscriptionID"

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
                              -SubscriptionId "sourceSubscriptionID" `
                              -ResourceGroupName "sourceRGName" `
                              -InstanceName "sourceManagedInstanceName" `
                              -Name "sourceDatabaseName" `
                              -PointInTime $pointInTime `
                              -TargetInstanceDatabaseName "targetDatabaseName" `
                              -TargetInstanceName "targetManagedInstanceName" `
                              -TargetResourceGroupName "targetResourceGroupName" `
                              -TargetSubscriptionId "targetSubscriptionId"

Use the Azure CLI to restore your database to a point in time. For more information, see Install the Azure CLI. For a detailed explanation of available parameters, see the CLI documentation for restoring a database in SQL Managed Instance.

Run one of the following code options with your values substituted for the parameters.

To restore the database to the same managed instance:

az sql midb restore -g mygroupname --mi myinstancename |
-n mymanageddbname --dest-name targetmidbname --time "2018-05-20T05:34:22"

To restore the database to a different managed instance, also specify the names of the target resource group and the managed instance:

az sql midb restore -g mygroupname --mi myinstancename -n mymanageddbname |
       --dest-name targetmidbname --time "2018-05-20T05:34:22" |
       --dest-resource-group mytargetinstancegroupname |
       --dest-mi mytargetinstancename

To restore to another subscription, be sure to set the context (az account set) to the target subscription:

az account set -s "targetSubscriptionId" `

az sql midb restore -s sourcesubscriptionid -g sourcegroup 
--mi sourceinstance -n sourcemanageddb --dest-name targetDbName 
--dest-mi targetMI --dest-resource-group targetRG --time "2022-05-20T05:34:22"

Restore a deleted database

You can restore a deleted database by using the Azure portal, Azure PowerShell or the Azure CLI.

To restore a deleted managed database by using the Azure portal:

In the Azure portal, go to your source managed instance.
In the left menu under Data management, select Backups.
Under Show databases, select Deleted.
For the database to restore, select Restore.
In Create Azure SQL Managed database, enter or select details for the target managed instance to restore your database to. Select the Data source tab.
In Data source, enter or select the details for your source database. Select the Additional settings tab.
In Additional settings, configure retention settings. Select the Review + create tab.
In Review + create, select Create to restore your deleted database.

To restore a deleted managed database, run one of the following PowerShell code options with your values substituted for the parameters:

To restore a deleted database to the same managed instance:

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

$resourceGroupName = "<resource group name>"
$managedInstanceName = "<managed instance name>"
$deletedDatabaseName = "<source database name>"
$targetDatabaseName = "<target database name>"

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

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

To restore the database to a different managed instance, you also specify the names of the target resource group and the target managed instance:

$targetResourceGroupName = "<resource group of target managed instance>"
$targetInstanceName = "<target managed instance name>"

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

To restore the database to another subscription, set the context to the the target subscription (Set-AzContext) and be sure to provide values for the required parameters -TargetSubscriptionID, and -DeleteDate:

Set-AzContext -SubscriptionID "targetSubscriptionID"

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
                              -SubscriptionId "sourceSubscriptionID" `
                              -ResourceGroupName "sourceRGName" `
                              -InstanceName "sourceManagedInstanceName" `
                              -Name "sourceDatabaseName" `
                              -PointInTime $pointInTime `
                              -TargetInstanceDatabaseName "targetDatabaseName" `
                              -TargetInstanceName "targetManagedInstanceName" `
                              -TargetResourceGroupName "targetResourceGroupName" `
                              -TargetSubscriptionId "targetSubscriptionId" `
                              -DeletionDate "deletion_date"

To restore a deleted database to the same subscription:

az sql midb restore -g resourcegroup --mi instancename
-n databasename --dest-name databasename --dest-mi instancename 
--dest-resource-group resourcegroup --time "2023-02-23T11:54:00" --deleted-time "deletion_date"

To restore a deleted database to another subscription, be sure to set the context (az account set) to the target subscription and specify the -s parameter for the az sql midb restore command to identify the source subscription:

az account set -s "targetSubscriptionId"

az sql midb restore -s sourcesubscriptionid -g sourcegroup 
--mi sourceinstance -n sourcemanageddb --dest-name targetDbName 
--dest-mi targetMI --dest-resource-group targetRG 
--time "2022-05-20T05:34:22" --deleted-time "deletion_date"

Overwrite an existing database

To overwrite an existing database, you must do the following:

Drop the original database that you want to overwrite.
Rename the database restored from the point-in-time to the name of the database you dropped.

Drop the original database

You can drop the database by using the Azure portal, PowerShell, or the Azure CLI.

Another option to drop the database is to connect to your managed instance directly in SQL Server Management Studio (SSMS), and then use the DROP Transact-SQL (T-SQL) command:

DROP DATABASE WorldWideImporters;

Use one of the following methods to connect to the database in your managed instance:

In the Azure portal, select the database in your managed instance.
In the command bar, select Delete.

To delete an existing database from your managed instance, run the following PowerShell code with your values substituted for the parameters:

$resourceGroupName = "<resource group name>"
$managedInstanceName = "<managed instance name>"
$databaseName = "<source database name>"

Remove-AzSqlInstanceDatabase -Name $databaseName -InstanceName $managedInstanceName -ResourceGroupName $resourceGroupName

To delete an existing database from your managed instance, run the following Azure CLI code with your values substituted for the parameters:

az sql midb delete -g mygroupname --mi myinstancename -n mymanageddbname

Change the new database name to match the original database name

Use SQL Server Management Studio (SSMS) to connect directly to your managed instance. Then run the following T-SQL query. The query changes the name of the restored database to the name of the dropped database you intend to overwrite.

ALTER DATABASE WorldWideImportersPITR MODIFY NAME = WorldWideImporters;