Edit

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

  • Article

Many applications have regulatory, compliance, or other business purposes that require you to retain database backups beyond the 1-35 days provided by automatic backups. By using the long-term retention (LTR) feature, you can store specified SQL Database and SQL Managed Instance full backups in Azure Blob storage with configurable redundancy up to 10 years. LTR backups can then be restored as a new database. For more information, see Automated backups in Azure SQL Database or Automated backups in Azure SQL Managed Instance.

Long-term retention can be enabled for Azure SQL Database, including the Hyperscale service tier, and for Azure SQL Managed Instance. This article provides a conceptual overview of long-term retention. To configure long-term retention, see Configure Azure SQL Database LTR and Configure Azure SQL Managed Instance LTR.

Note

  • In Azure SQL Managed Instance, use SQL Agent jobs to schedule copy-only database backups as an alternative to LTR beyond 35 days.

How long-term retention works

Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point in time restore (PITR). If an LTR policy is configured, these backups are copied to different blobs for long-term storage. 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 year (WeekOfYear). If you specify W, one backup every week will be copied to the long-term storage. If you specify M, the first backup of each month will be copied to the long-term storage. If you specify Y, one backup during the week specified by WeekOfYear will be copied to the long-term storage. If the specified WeekOfYear is in the past when the policy is configured, the first LTR backup will be created in the following year. Each backup will be kept in the long-term storage according to the policy parameters that are configured when the LTR backup is created.

Note

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 will be kept for five years.

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

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

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

    Each weekly full backup will be kept for 12 weeks.

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

    Each weekly full backup will be kept for six weeks. Except first full backup of each month, which will be kept for 12 months. Except the full backup taken on 20th week of year, which will be 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 (week after May 13)

ltr example

If you modify the above policy and set W=0 (no weekly backups), Azure 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.

Important

The timing of individual LTR backups is controlled by Azure. You cannot manually create an LTR backup or control the timing of the backup creation. After configuring an LTR policy, it may take up to 7 days before the first LTR backup will show up on the list of available backups.

If you delete a logical server or a managed instance, all databases on that server or managed instance are also deleted and can't be recovered. You can't restore a deleted server or managed instance. However, if you had configured LTR for a database or managed instance, LTR backups are not deleted, and they can be used to restore databases on a different server or managed instance in the same subscription, to a point in time when an LTR backup was taken.

Similarly if you delete a database, LTR backups are not deleted and are retained for the configured retention period. These backups can be restored to the same server or a different server in the same subscription.

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 won't increase as backups aren't generated from the secondaries. The backups are only created when the secondary becomes primary. It ensures non-interrupted generation of the LTR backups when the failover is triggered and the primary moves to the secondary region.

Note

When the original primary database recovers from an outage that caused the failover, it will become a new secondary. Therefore, the backup creation will not resume and the existing LTR policy will not take effect until it becomes the primary again.

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.

Note

If you are using LTR backups to meet compliance or other mission-critical requirements, consider conducting periodic recovery drills to verify that LTR backups can be restored, and that the restore results in expected database state.

Next steps

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