Manage Azure SQL Managed Instance long-term backup retention

Applies to: Azure SQL Managed Instance

In Azure SQL Managed Instance, you can configure a long-term backup retention policy (LTR). This allows you to automatically retain database backups in separate Azure Blob storage containers for up to 10 years. You can then recover a database using these backups with the Azure portal, the Azure CLI and PowerShell.

The following sections show you how to use the Azure portal, PowerShell, and Azure CLI to configure the long-term backup retention, view backups in Azure SQL storage, and restore from a backup in Azure SQL storage.

Prerequisites

An active Azure subscription.

Permissions

To view and restore LTR backups, you need to be a member of the following roles:

  • Subscription Owner role or
  • SQL Managed Instance Contributor role or
  • Custom role with the following permissions:
    • Microsoft.Sql/locations/longTermRetentionManagedInstanceBackups/read
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionManagedInstanceBackups/read
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/read

To delete LTR backups, you need to be a member of one of the following roles:

  • Subscription Owner role or
  • Custom role with the following permission:
    • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/delete

Note

The SQL Managed Instance Contributor role does not have permission to delete LTR backups.

Azure role-based access control (RBAC) permissions could be granted in either subscription or resource group scope. However, to access LTR backups that belong to a dropped instance, the permission must be granted in the subscription scope of that instance.

  • Microsoft.Sql/locations/longTermRetentionManagedInstances/longTermRetentionDatabases/longTermRetentionManagedInstanceBackups/delete

Create long-term retention policies

You can configure SQL Managed Instance to retain automated backups for a period longer than the retention period for your service tier.

  1. In the Azure portal, select your managed instance and then select Backups. On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Changes will not apply to any databases left unselected.

    A screenshot from the Azure portal showing the Manage backups link.

  2. In the Configure policies pane, specify your desired retention period for weekly, monthly, or yearly backups. Choose a retention period of '0' to indicate that no long-term backup retention should be set.

    A screenshot from the Azure portal showing how to Configure policies.

  3. When complete, select Apply.

Important

When you enable a long-term backup retention policy, it may take up to 7 days for the first backup to become visible and available to restore. For details of the LTR backup cadence, see long-term backup retention.

View backups and restore from a backup

To view available long-term backups from the Azure portal, follow these steps:

  1. In the Azure portal, select your managed instance and then select Backups. On the Available backups tab, select the database for which you want to see available backups. Select Manage.

    A screenshot from the Azure portal showing the option to select a database.

  2. In the Manage backups pane, review the available backups.

    A screenshot from the Azure portal showing the page to view backups.

You can also restore from this page by choosing the backup and selecting Restore.

Alternatively, to restore a backup from long-term retention by using the Azure portal, 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 dropdown list. 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 dropdown list, 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 long-term retention 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.

Note

From here, you can connect to the restored database using SQL Server Management Studio to perform needed tasks, such as to extract a bit of data from the restored database to copy into the existing database or to delete the existing database and rename the restored database to the existing database name.

Delete LTR backups

Delete backups that are retained for a specific database with an LTR policy.

Important

Deleting LTR backup is non-reversible. To delete an LTR backup after the instance has been deleted you must have Subscription scope permission. You can set up notifications about each delete in Azure Monitor by filtering for operation 'Deletes a long term retention backup'. The activity log contains information on who and when made the request. See Create activity log alerts for detailed instructions.

  1. In the Azure portal, navigate to the SQL managed instance.
  2. Select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane appears with a list of the available LTR backups for the selected database.
  3. In the Available LTR backups pane that appears, review the available backups. Select a backup to delete. Select Delete.

Limitations

Database backups taken from instances configured with the SQL Server 2022 update policy can be restored to instances configured with either the SQL Server 2022 or Always-up-to-date update policy. Database backups taken from instances configured with the Always-up-to-date update policy can only be restored to instances also configured with the Always-up-to-date update policy.

Next steps