Manage Azure SQL Database long-term backup retention
Applies to: Azure SQL Database
With Azure SQL Database, you can set a long-term backup retention policy (LTR) to automatically retain backups in separate Azure Blob storage containers for up to 10 years. You can then recover a database using these backups using the Azure portal, Azure CLI, or PowerShell. Long-term retention policies are also supported for Azure SQL Managed Instance.
Create long-term retention policies
You can configure SQL Database to retain automated backups for a period longer than the retention period for your service tier.
In the Azure portal, navigate to your server and then select Backups. Select the Retention policies tab to modify your backup retention settings.
On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Unselected databases will not be affected.
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.
Select Apply to apply the chosen retention settings to all selected databases.
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
View the backups that are retained for a specific database with an LTR policy, and restore from those backups.
In the Azure portal, navigate to your server and then select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane will appear with a list of the available LTR backups for the selected database.
In the Available LTR backups pane that appears, review the available backups. You may select a backup to restore from or to delete.
To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.
Choose a name for your new database, then select Review + Create to review the details of your Restore. Select Create to restore your database from the chosen backup.
On the toolbar, select the notification icon to view the status of the restore job.
When the restore job is completed, open the SQL databases page to view the newly restored database.
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.
- When restoring from an LTR backup, the read scale property is disabled. To enable, read scale on the restored database, update the database after it has been created.
- You need to specify the target service level objective, when restoring from an LTR backup, which was created when the database was in an elastic pool.
- LTR in the Hyperscale service tier is in limited public preview
LTR can be enabled for Hyperscale databases created or migrated from other service tiers after June 2022. LTR support for all other Hyperscale databases will be added over the next several weeks. If you attempt to enable LTR for a Hyperscale database where it is not yet supported, you will receive the following error: "Long Term Retention is not supported: Long-term retention on Hyperscale is currently in limited preview and cannot be enabled as yet for your database. To enable long-term retention please reach out to Microsoft support." Please create a support ticket to resolve this, or wait until LTR support is enabled for your database.
If you use LTR backups to meet compliance or other mission-critical requirements:
- Verify the LTR backups are taken as per the configured policy by following steps outlined in view backups section either using Portal, Azure CLI or PowerShell.
- Consider conducting periodic recovery drills to verify that restore of LTR backups results in expected database state.