Many applications have regulatory, compliance, or other business purposes that require you to retain database backups beyond the 7-35 days provided by Azure SQL Managed Instance automated backups. By using the long-term retention (LTR) feature, you can store the specified full backup of a SQL Managed Instance database in Azure Blob storage with configured redundancy for up to 10 years. You can then restore any backup as a new database. Long-term retention is available in Public Preview for Azure SQL Managed Instance.
Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point-time restore (PITR). If an LTR policy is configured, these backups are to different for long-term storage. The LTR policy for each database can also specify how frequently the LTR backups are created. To enable LTR, you can define a policy using a combination of four parameters: weekly backup retention (W), monthly backup retention (M), yearly backup retention (Y), and week of year (WeekOfYear). To learn more about how to configure long-term retention policy, see here.
By default, SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. When an Azure region is unavailable, geo-restore can be used to recover the database in a different Azure region. Geo-restore is only available if backups are geo-redundant.
However, to meet data residency requirements, managed instance backups can now be configured as local or zone redundant. This ensures that the database backups stay within the same region. The configured backup storage redundancy for a given managed instance applies to both PITR backups and long-term retention backups (LTR).
For SQL Managed Instance, the backup storage redundancy must be selected at the time of creation of the managed instance. Updating backup storage redundancy for an existing managed instance is not supported yet.
In this section we will describe how you can configure long-term retention for your SQL Managed Instance database using Azure Portal. Here are the RBAC roles that you will need while operating with the long-term retention backups.
When you setup long-term retention for a database, you may perform the following operations on your backups:
We will look into each of these and understand how this can be done.
To setup a new LTR policy for a database:
To list all the available LTR backups for a given database:
There are 2 different methods that you can use on Azure Portal to restore using a long-term retention backup.
3. Clicking on Restore will open the full-screen database restore experience on Azure Portal. Here, you will see the long-term retention source backup. A name for the new database will be pre-selected based on the timestamp, you can change the database name here and set it to desired value.
4. To delete a backup, which is no longer required, instead of clicking Restore, click the Delete button. This will delete the selected long-term retention backup(s). Note that, deleting a long-term retention backup is an irreversible operation. Once deleted, these backups cannot be recovered.
To restore LTR backup of a deleted database:
To analyze backup costs, you can use the Cost Analysis tool available at the subscription level on Azure Portal. Details of how to look up different backup costs are described here. In this section, we will look at how you can look up your LTR backup costs.
To meet your organizations compliance or regulatory needs, you can now easily configure long-term retention backups for up to 10 years for Azure SQL managed instance database(s). This can be easily done using Azure Portal or Azure PowerShell. You can also pick the desired backup storage redundancy for backups, to meet your data residency requirements.
If you find this article useful, please like it on this page and share through social media. To share this article, you can use the Share button below, or this short link: https://aka.ms/LTRonMI.
Please note that products and options presented in this article are subject to change. This article reflects the long-term retention backup options available for Azure SQL Managed Instance in March, 2021.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.