Azure custom policy to prevent backup retention period to be below X number – Azure SQL

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

In this blog article, we will cover how to prevent and deny the automated backup retention days for Azure SQL database to be lower than X number of days.

We have received some service requests from customers that would like to ensure that they have for example 14 days as a retention period for their automated backup and would like to do this using a custom policy since they have multiple databases.

You can follow the steps below to create a custom policy and ensure this:

1) From the Azure portal, access Azure policy, then definitions blade.

2) Create a new policy definition.

3) Add the definition location (which subscription will be hosting this policy), Name, and description.

4) Set the category to use existing and select SQL (as below):

5) Then add the below policy definition into the rule field:

Note: you can adjust the below format to have tags or parameters as needed, also the below example is set to 14 days as retention period days. 

{
  "mode": "All",
  "policyRule": {
    "if": {
      "allOf": [
        {
          "field": "type",
          "equals": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies"
        },
        {
          "anyOf": [
            {
              "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
              "exists": false
            },
            {
              "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
              "less": 14
            }
          ]
        }
      ]
    },
    "then": {
      "effect": "deny"
    }
  },
  "parameters": {}
}

6) Then save the policy.

Now since the policy definition has been created, you can add an assignment that refers to which resource will have this policy applied on.

From Azure policies page, and access definitions blade -> select the created custom policy, and click assign policy (you can assign on the Subscription level or a specific resource group depending on your business requirements).

After the assignment, if you try to have the retention backup days below 14 days, you can expect the below error:

References

Automatic, geo-redundant backups - Azure SQL Database | Microsoft Learn

Tutorial: Create a custom policy definition - Azure Policy | Microsoft Learn

Disclaimer
Please note that products and options presented in this article are subject to change. This article reflects automated backups retention days custom policy for Azure SQL Database in October, 2023.

Closing remarks

I hope this article was helpful for you, please like it on this page and share it through social media. Please feel free to share your feedback in the comments section below. 

I hope this article was helpful for you, please feel free to share your feedback in the comments section.