Restore a database from a backup in Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

This article provides steps to recover a database from a backup in Azure SQL Managed Instance. For Azure SQL Database, see Restore a database from a backup in Azure SQL Database.

Overview

Automated database backups help 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. The following options are available for database recovery through automated backups:

  • Create a new database on the same managed instance, recovered to a specified point in time within the retention period.
  • Create a new database on the same managed instance or a different managed instance, recovered to a specified point in time within the retention period.
  • Create a database on the same managed instance or a different managed instance, recovered to the deletion time for a deleted database.
  • Create a new database on any managed instance in same subscription or different subscription in same tenant and in the same region, recovered to the point of the most recent backups.

If you configured long-term retention (LTR), you can also create a new database from any long-term retention backup on any instance.

Important

You can't overwrite an existing database during restore.

Recovery time

Several factors affect the recovery time to restore a database through automated database backups:

  • 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 that are processed in the target region

For a large or very active database, the restore might take several hours. A prolonged outage in a region might cause a high number of geo-restore requests for disaster recovery. When there are many requests, the recovery time for individual databases can increase. Most database restores finish in less than 12 hours.

Tip

For Azure SQL Managed Instance, system updates take precedence over database restores in progress. If there's a system update for SQL Managed Instance, all pending restores are suspended and then resumed after the update has been applied. This system behavior might prolong the time of restores and might be especially impactful to long-running restores.

To achieve a predictable time of database restores, consider configuring maintenance windows that allow scheduling of system updates at a specific day and time. Also consider running database restores outside the scheduled maintenance window.

Permissions

To recover by using automated backups, 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

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

You can recover by using the Azure portal, PowerShell, or the REST API. You can't use Transact-SQL.

Point-in-time restore

You can restore a database to an earlier point in time. The request can specify any service tier or compute size for the restored database. Ensure that you have sufficient resources on the instance to which you're restoring the database.

When the restore is complete, it creates a new database on the target instance, whether it's the same instance, or a different instance. 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.

Important

You can't perform a point-in-time restore on a geo-secondary database. You can do so only on a primary database.

  • Database replacement

    If you want the restored database to be a replacement for the original database, you should specify the original database's compute size and service tier. 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 run a data recovery script that extracts data from the restored database and applies to the original database. Although the restore operation might 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. You won't be charged for the database that didn't complete the restore.

To recover a database in SQL Managed Instance to a point in time by using the Azure portal, you can go to the database in the portal and choose Restore. Alternatively, you can open the target SQL Managed Instance overview page, and select + New database on the toolbar 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.

Provide target managed instance details on the Basics tab, and choose a type of backup from the Data source tab.

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.

For greater details, review the Point in time restore article.

Deleted database restore

You can restore a deleted database to the deletion time, or an earlier point in time, to the same instance, or a different instance than the source instance. The target instance can be in the same subscription or in a different subscription than the source instance. You restore a deleted database by creating a new database from the backup.

Important

You can't restore a deleted managed instance. If you delete a managed instance, all its databases are also deleted and can't be restored to the deletion time, or an earlier point in time. If you configured long-term retention (LTR), you can still restore a database from deleted instance to another instance and to point in time when LTR backup was taken.

To recover a database by using the Azure portal, open the managed instance's overview page and select Backups. Choose to show Deleted backups, and then select Restore next to the deleted backup you want to recover to open the Create Azure SQL Managed Database page. Provide target managed instance details on the Basics tab, and source managed instance details on the Data source tab. Configure retention settings on the Additional settings tab.

Screenshot of the Azure portal, Backups page of the SQL Managed Instance, showing deleted databases and selecting the Restore action.

Tip

It might take several minutes for recently deleted databases to appear on the Deleted databases page in the Azure portal, or when you want to display deleted databases by using the command line.

Geo-restore

Important

  • Geo-restore is available only for managed instances configured with geo-redundant backup storage. If you're not currently using geo-replicated backups for a database, you can change this by configuring backup storage redundancy.
  • You can perform geo-restore on managed instances that reside in the same subscription only.

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 an instance in any other region. You can restore a 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 a geo-restore even if an outage has made the database or datacenter inaccessible.

There's a delay between when a backup is taken and when it's 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.

Illustration of restoring a database across regions for the purpose of geo-restore.

From the Azure portal, you can restore a geo-replicated backup to an existing instance, or you create a new managed instance and select an available geo-restore backup. The newly created database contains the geo-restored backup data.

To restore to an existing instance, follow the steps in Point-in-time restore, and be sure to choose the appropriate source and target instances to restore your database to your intended instance.

To geo-restore to a new instance by using the Azure portal, follow these steps:

  1. Go to your new Azure SQL managed instance.
  2. Select New database.
  3. Enter a database name.
  4. Under Data source, choose the appropriate type of backup, and then provide details for the data source.
  5. Select a backup from the list of available geo-restore backups.

After you complete the process of creating an instance database, it will contain the restored geo-restore backup.

Geo-restore considerations

Geo-restore is the most basic disaster-recovery solution available in Azure SQL Managed Instance. It relies on automatically created geo-replicated backups in a secondary (paired) region. Here're some considerations for geo-restore:

  • Recovery point objective (RPO) is up to 1 hour.
  • Restore processes (recovery time objective - RTO) usually take less than 12 hours but might vary based on database size and activity so restoration could extend beyond this time frame.
  • Secondary (paired) region is Azure storage settings for the primary region. You can't change the secondary region.
  • Newly created/restored databases might not immediately appear as restorable in other regions due to a lag in populating new data. If customers do not see the backups of new databases, they should anticipate a waiting period of up to 24 hours.

It's essential to acknowledge that geo-restore serves as an appropriate disaster-recovery solution for applications with relatively small databases that aren't critical to the business. For business-critical applications that require large databases and must ensure business continuity, use failover groups. That feature offers a much lower RPO and RTO, and the capacity is always guaranteed.

For more information about business continuity choices, see Overview of business continuity.

Limitations

Consider the following limitations when working with backups and Azure SQL Managed Instance:

  • Geo-restore of a database can only be performed to an instance in the same subscription as the source SQL managed instance.
  • Azure SQL Managed Instance databases are encrypted with TDE by default. When the source database uses a customer-managed key (CMK) as the TDE protector, to restore your database to an instance other than the source SQL Managed Instance, the target instance must have access to the same key used to encrypt the source database in Azure Key Vault, or you must disable TDE encryption on the source database before taking the backup.
  • You can only track the progress of the restore process by using the sys.dm_exec_requests and sys.dm_operation_status dynamic management views.
  • Recovery point objective (RPO) is up to 1 hour.
  • Recovery time objective (RTO) is approximately 12 hours, but might vary based on database size and activity could go beyond this timeframe.
  • Secondary (paired) region can't be changed.
  • Newly created/restored databases might not immediately appear as restorable in other regions due to a lag in populating new data. It can take up to 24 hours for backups of new database to become visible.
  • The maximum number of databases you can restore in parallel is 200 per single subscription. In some cases, it's possible to increase this limit by opening a support ticket.

Related content