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.
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.
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.
If you try to set the 35 days retention period, you get the following error.
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.
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.
Click on yes for saving the LTR policy for your Azure SQL database.
As shown below, it updates the backup dashboard from your LTR policy.
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.
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.
|
1 2 3 4 5 6 |
$subId = "c6eb5552-7748-4d57-82bb-4c52c1c8f87e" $serverName = "azuredemoinstance" $resourceGroup = "azuresqldemo" $dbName = "labazuresql" $server = Get-AzSqlServer -ServerName $serverName -ResourceGroupName $resourceGroup $server |
In the output, you get SQL Server properties such as location, version, tags, fully qualified domain name.
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.
|
1 2 |
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName ` -ResourceGroupName $resourceGroup -WeeklyRetention P05W |
In the query output, it returns the configured LTR policy.
Suppose you want to modify the weekly retention to 15 weeks, you can rerun the script with an updated retention value.
|
1 2 |
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName ` -ResourceGroupName $resourceGroup -WeeklyRetention P15W |
Refresh your Azure portal, and you can view the updated LTR policy.
In the below script, we configure the monthly backup retention to 2 years.
|
1 |
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup -MonthlyRetention P2Y |
It gets reflected in the backup dashboard, as shown below.
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)
|
1 2 |
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup ` -WeeklyRetention 10 -MonthlyRetention P20W -YearlyRetention P05Y -WeekOfYear 23 |
In the Azure SQL database backup dashboard, you get configured weekly, monthly and yearly backup retentions.
-
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.
It removes the LTR for the selected databases. Click on OK.
It removes configured LTR and shows up the default policy as per your service tier.
You can also specify the -RemovePolicy clause to remove the policy from the Azure PowerShell.
|
1 |
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName -ResourceGroupName $resourceGroup -RemovePolicy |
As shown below, we get 0 value for 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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023