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

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.
  • You can only restore a backup from the primary region. Restoring a database from the geo-replicated secondary region is not 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.

To restore an existing database, you can do so by going to the database page in the Azure portal, and selecting Restore.

Alternatively to restore your database, you can follow these steps:

  1. Sign in to the Azure portal.

  2. Go to the target SQL Managed Instance where you plan to restore your database to.

  3. On the Overview page, choose + New database to open the Create Azure SQL Managed Database page.

    Screenshot that shows the SQL Managed Instance overview pane in the Azure portal, with adding a new database selected.

  4. On the Basics tab of the Create Azure SQL Managed Database page, provide subscription and resource group details under Project details. Then, under Database details provide the new name of the database you plan to restore. Confirm the correct managed instance is listed in the drop down. Then select Next: Data source >

    Screenshot of the Azure portal that shows the Basics tab of the Create Azure SQL Managed Database page.

  5. On the Data source tab, choose Point-in-time restore under Use existing data. Provide the subscription, resource group and managed instance that contains the source database. From the Managed database drop-down, choose the database you want to restore, and then choose the point in time you want to restore the database from. The source and target instance can be the same, or two different instances. Select Next : Additional settings >

    Screenshot of the Azure portal that shows the data source tab of the Create Azure SQL Managed Database page, with point-in-time restore selected.

  6. On the Additional settings tab, you can check the box to inherit the retention policy from the source database, or, alternatively, you can select Configure retention to open the Configure policies page, and set your desired retention policies for your restored database. When finished, select Review + create.

    Screenshot of the Azure portal that shows the additional settings tab of the Create Azure SQL Managed Database page.

  7. On 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.

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:

  1. In the Azure portal, go to your source managed instance.

  2. In the left menu under Data management, select Backups.

  3. Under Show databases, select Deleted.

  4. For the database to restore, select Restore.

    Screenshot that shows available databases in the portal, with the Restore button highlighted to restore a deleted database.

  5. 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.

  6. In Data source, enter or select the details for your source database. Select the Additional settings tab.

  7. In Additional settings, configure retention settings. Select the Review + create tab.

  8. In Review + create, select Create to restore your deleted database.

Overwrite an existing database

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

  1. Drop the original database that you want to overwrite.
  2. 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:

  1. In the Azure portal, select the database in your managed instance.

  2. In the command bar, select Delete.

    Screenshot that shows how to delete a database by using the Azure portal.

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;

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

Next steps

Learn about automated backups.