Long-term retention - Azure SQL Database and Azure SQL Managed Instance

Applies to: Azure SQL Database Azure SQL Managed Instance

This article provides a conceptual overview of long-term retention of backups for Azure SQL Database and Azure SQL Managed Instance. Long-term retention can be configured for up to 10 years on backups for Azure SQL Database (including in the Hyperscale service tier), and Azure SQL Managed Instance.

To get started, see configure long-term backup retention for Azure SQL Database and Azure SQL Managed Instance.

How long-term retention works

Many applications have regulatory, compliance, or other business reasons that require you to retain database backups beyond the 1-35 days provided by the short-term retention period of automatic backups. Long-term backup retention (LTR) relies on the full database backups that are automatically created by the Azure SQL service. For more information, see automated backups in Azure SQL Database or Azure SQL Managed Instance.

By using the LTR feature, you can store specified full SQL Database and SQL Managed Instance backups in redundant Azure Blob storage with a configurable retention policy of up to 10 years. LTR backups can then be restored as a new database. If an LTR policy is configured, automated backups are copied to different blobs for long-term storage which you can then use to restore your database to a specific point in time. The copy is a background job that has no performance impact on the database workload. The LTR policy for each database in SQL 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 the year (WeekOfYear). If you specify W, one backup every week is copied to long-term storage. If you specify M, the first backup of each month is copied to the long-term storage. If you specify Y, one backup during the week specified by WeekOfYear is copied to the long-term storage. If the specified WeekOfYear is in the past when the policy is configured, the first LTR backup is created the following year. Each backup is kept in long-term storage according to the policy parameters that are configured when the LTR backup is created.

Any change to the LTR policy applies only to future backups. For example, if weekly backup retention (W), monthly backup retention (M), or yearly backup retention (Y) is modified, the new retention setting will only apply to new backups. The retention of existing backups will not be modified. If your intention is to delete old LTR backups before their retention period expires, you will need to manually delete the backups.

Examples of the LTR policy:

• W=0, M=0, Y=5, WeekOfYear=3

  The third full backup of each year is kept for five years.

• W=0, M=3, Y=0

  The first full backup of each month is kept for three months.

• W=12, M=0, Y=0

  Each weekly full backup is kept for 12 weeks.

• W=6, M=12, Y=10, WeekOfYear=20

  Each weekly full backup is kept for six weeks. Except the first full backup of each month, which is kept for 12 months. Except the full backup taken on the 20th week of the year, which is kept for 10 years.

The following table illustrates the cadence and expiration of the long-term backups for the following policy:

W=12 weeks (84 days), M=12 months (365 days), Y=10 years (3650 days), WeekOfYear=20 (the week after May 13)

The following dates are in ISO 8601 (YYYY-MM-DD).

If you modify the above policy and set W=0 (no weekly backups), the service only retains the monthly and yearly backups. No weekly backups are stored under the LTR policy. The storage amount needed to keep these backups reduces accordingly.

Geo-replication and long-term backup retention

If you're using active geo-replication or failover groups as your business continuity solution, you should prepare for eventual failovers and configure the same LTR policy on the secondary database or instance. Your LTR storage cost doesn't increase, as backups aren't generated from the secondaries. The backups are only created when the secondary becomes primary. It ensures noninterrupted generation of the LTR backups when the failover is triggered and the primary moves to the secondary region.

Configure long-term backup retention

You can configure long-term backup retention using the Azure portal and PowerShell for Azure SQL Database and Azure SQL Managed Instance. To restore a database from the LTR storage, you can select a specific backup based on its timestamp. The database can be restored to any existing server or managed instance under the same subscription as the original database.

To learn how to configure long-term retention or restore a database from backup for SQL Database using the Azure portal or PowerShell, see Manage Azure SQL Database long-term backup retention.

To learn how to configure long-term retention or restore a database from backup for SQL Managed Instance using the Azure portal or PowerShell, see Manage Azure SQL Managed Instance long-term backup retention.

When a restore request is initiated in the final 7 days of the LTR retention period, Azure will automatically extend the expiration date of all backups +7 days, to prevent an LTR backup from expiring during the restore.

Related content

Because database backups protect data from accidental corruption or deletion, they're an essential part of any business continuity and disaster recovery strategy.

• Business continuity overview for Azure SQL Database
• Business continuity overview for Azure SQL Managed Instance
• Automated backups in Azure SQL Database
• Automated backups in Azure SQL Managed Instance

For a tutorial on configuring and managing LTR backups, visit:

• Manage Azure SQL Database long-term backup retention
• Manage Azure SQL Managed Instance long-term backup retention