Rajendra Gupta
Configure the long-term retention policy

Configure long-term backup retention for Azure SQL database

March 25, 2021 by

This article configures a long-term backup retention policy for Azure SQL Database.

Introduction

It is the prime duty of database administrators (DBAs) to ensure all databases are backed up regularly as per the defined backup policy. These backups should be available for restoration on-demand as well in case of any incident happens. Usually, for the traditional systems, you define a backup retention period depending upon your database nature and criticality. For example, you might require longer backup retention for a customer or finance-related data while for shorter backup retention for non-critical databases.

If you have moved to an Azure SQL database, you should be familiar with the default retention period. If there is a requirement to store the backups longer than the default retention period, what would you do?

In this article, let’s explore the answer to the above-specified questions.

Default backup retention period in Azure SQL database

The default retention period in the Azure SQL database depends on the service tier level. Broadly, we have three service tiers- Basic, Standard and Premium for Azure SQL databases In the below table, we get the default retention period for these tiers.

  • Basic: 7 days of retention
  • Standard tier: 35 days
  • Premium tier: 35 days

If you want to check the default backup retention for your Azure Server, connect to the Azure portal and click on Managed backups.

As you can see below, for my lab environment, the pricing tier Basic shows the 7 days retention period.

Azure pricing tiers

For a basic tier, you can change maximum backup retention from 1 -7 days. To modify it, select your database and click on Configure retention.

maximum backup retention

It opens a page for configuring backup policies. You might be wondering that it gives you the option for configuring 35 days as well for a basic tier.

configuring backup policies

If you try to set the 35 days retention period, you get the following error.

35 days retention period

Long-term backup retention for Azure SQL database

The long-term retention (LTR) enables you to configure backup redundancy for up to 10 years. It is available for both Azure SQL Database and Managed instances. It stores your automatic full database backups in Azure storage for long-term retention. Once you configure the LTR policy, Azure copies these backups to a different blob in an asynchronous way. The copy operation does not put any performance hindrance to your database workload.

The LTR policy is a combination of the following parameters:

  • W: Weekly backup retention
  • M: Monthly backup retention
  • Y: Yearly backup retention
  • WeekOfYear: Week of the year retention

You can use either one or all four parameters for defining backup policy. In the below table, I define a few examples of LTR policy.

LTR policy

Description

W=0, M=0, Y=7, WeekOfYear=5

The 5th full backup of each year is stored with 7-year retention.

W=10, M=0, Y=0

It stores each weekly full backup for 10 weeks.

W=0, M=5, Y=0

It stores the first full backup of each month for five months.

W=4, M=12, Y=5, WeekOfYear=25

It stores each weekly full backup for 4 weeks. The database first full backup of each month is stored for 12 months. Along with this, the backup on the 25th week of the year is stored for five years.

To configure the long-term retention policy, go to the Configure policies and Long-term Retention Configurations.

Long-term backup retention

In this policy configuration, you get an option to enable weekly, monthly and yearly LTR backups with their retention period.

  • Weekly LTR backups: As defined earlier, it refers to the weekly backup retention period. You can define it in the following ways
    • Day: It can have a minimum of 7 and a maximum of 3650 days
    • Weeks: Minimum value 1 and Maximum 520 weeks
    • Months: Minimum 1 to a maximum of 120 months
    • Years: Minimum 1 year to 10 years
  • Monthly LTR backups: It refers to the monthly backup retention period. It allows the following values
    • Day: You can use the value of a minimum of 30 and a maximum of 3650 days
    • Weeks: Minimum value 4 and Maximum 520 weeks
    • Months: Minimum 1 to a maximum of 120 months
    • Years: Minimum 1 year to 10 years
  • Yearly LTR backups: It defines the retention period for yearly backups. Once you put a check on it, you get another configuration option for specifying which weekly backup of the year we want to retain. You can specify a value from Week 1 to Week 52
    • Days: Value between 365 and 3650
    • Weeks: Specify retention value between 52 to 520
    • Months: Specify the value between 12 and 120
    • Years: Value from 1 to 10

Suppose we want to retain weekly backups for 2 months. In this case, enable the Weekly LTR backups and specify the value as shown below.

Weekly LTR backups

Click on yes for saving the LTR policy for your Azure SQL database.

Save the policy

As shown below, it updates the backup dashboard from your LTR policy.

backup dashboard

You can click on Available Long-term Retention backups for a list of LTR backups along with their expiration time. In my lab environment, I do not have backups eligible for the LTR policy. Therefore, it does not show any record in the below image.

Available long-term retention backups

Configure the long-term retention policy for Azure SQL Database using Azure PowerShell

In case you have multiple databases, it becomes easy to use Azure PowerShell scripts for managing the resources. We can use Set-AzSqlDatabaseBackupLongTermRetentionPolicy cmdlet for this purpose.

You can follow the article Create Azure SQL Database using Azure PowerShell for download, install and configure the Azure PowerShell module.

Connect to your Azure account using PowerShell and run the following command to return SQL Server properties.

In this command, you need to specify subscription ID, server name, resource group.

In the output, you get SQL Server properties such as location, version, tags, fully qualified domain name.

Configure the long-term retention policy

Use Set-AzSqlDatabaseBackupLongTermRetentionPolicy for configuring weekly LTR of 5 weeks

In the below command, we specify the server name, database name, resource group and value for weekly retention.

In the query output, it returns the configured LTR policy.

Use Set-AzSqlDatabaseBackupLongTermRetentionPolicy

Suppose you want to modify the weekly retention to 15 weeks, you can rerun the script with an updated retention value.

weekly retention to 15 weeks

Refresh your Azure portal, and you can view the updated LTR policy.

Validations

In the below script, we configure the monthly backup retention to 2 years.

monthly backup

It gets reflected in the backup dashboard, as shown below.

Verify LTR policy

Set an LTR policy for weekly, monthly and yearly retention values

In the below command, we set a long-term policy with the following specifications.

  • Weekly backup retention to 10 weeks ( using the parameter -weeklyRetention)
  • The first full backup of each month retention to 20 weeks. ( Using parameter -MonthlyRetention)
  • The full backup taken on the 23rd week has a retention period of 5 years ( Using parameter YearlyRetention and WeekofYear)

LTR policy for weekly, monthly and yearly retention values

In the Azure SQL database backup dashboard, you get configured weekly, monthly and yearly backup retentions.

Azure SQL Database backup

  • Note: If you specify a new LTR policy, it overrides the previous policy configurations.
    The LTR policy applies for backup taken after policy implementations. If we have taken a backup with another backup policy, it retains those configurations

Disable the long-term retention policy

You should disable the LTR policy for Azure SQL Database if you do not require it. For example, I performed the demonstration in my lab account. Therefore, I need to disable the LTR policy.

From the Azure portal, you can click on remove settings, and it clears the configured LTR policy.

Disable the long-term retention policy

It removes the LTR for the selected databases. Click on OK.

LTR for the selected databases

It removes configured LTR and shows up the default policy as per your service tier.

removes configured LTR

You can also specify the -RemovePolicy clause to remove the policy from the Azure PowerShell.

As shown below, we get 0 value for weekly, monthly and yearly retention.

Weekly, monthly and yearly retention.

Conclusion

In this article, we overviewed the default backup retention period for each service tier in the Azure SQL database. We also learned to configure the long-term backup retention based on your requirements.

Rajendra Gupta
Azure, Backup and restore, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

2,115 Views