Enforcing SQL PaaS backup retention with Azure Policy

Implementation for SQL DB PITR using the portal

Azure policy covers much more than SQL; here we are using a small portion of its capabilities. The bits we are using are

• A policy definition, on what the policy checks for, and what to do about issues
• A policy assignment, with the scope to check the definition across, and parameter values
• A remediation task, that makes the required changes

The requirement in this example is to ensure that all Azure SQL Databases have a short-term (PITR) backup retention of at least 9 days.

• Any database created without specifying the retention period will have this added
• Any update made with a shorter period will have that modified to be 9 days
• Modifications or database creation that explicitly set the retention period to more than 9 days will have that value honoured

All these are built under “Policy” in the portal

The definition

Open Policy | Authoring | Definitions, and on that blade, use the “+ Policy definition” to create a new one

• Definition Location: the subscription to hold this (there’s a pull-down menu of valid items)
• Name: for example, “Enforce SQL DB PITR”
• Category: for example “Backup”
• Role Definitions: “Contributor” for this example, but in general this should be the minimum needed for the updates that the definition will make
• Policy rule: 

{
  "mode": "All",
  "policyRule": {
    "if": {
      "anyOf": [
        {
          "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
          "exists": false
        },
        {
          "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
          "less": "[parameters('Minimum_PITR')]"
        }
      ]
    },
    "then": {
      "effect": "modify",
      "details": {
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c"
        ],
        "operations": [
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
            "value": "[parameters('Minimum_PITR')]"
          }
        ]
      }
    }
  },
  "parameters": {
    "Minimum_PITR": {
      "type": "Integer",
      "metadata": {
        "displayName": "Min PITR",
        "description": "Min PITR retention days"
      }
    }
  }
}

In this code

•          Field is what we want to check and/or change; get the list of field names using PowerShell

$aliases = Get-AzPolicyAlias -ListAvailable -NamespaceMatch 'Microsoft.Sql' 
| where ResourceType -like 'retentionpol' 
| Select-Object -ExpandProperty 'Aliases' $aliases | select Name

• For the list of fields that can be modified/updated, look at the Modifiable attribute

$aliases | Where-Object { $_.DefaultMetadata.Attributes -eq 'Modifiable' } 
| select Name 

• Minimum_PITR is the name of the parameter the assignment (next step) will pass in. You choose the name of the parameter
• roleDefinitionIds are the full GUID path of the roles that the update needs. The policy remediation docs talk about this, but we can get the GUID with PowerShell

Get-AzRoleDefinition -name 'contributor' # replace contributor with the role needed

This definition is saying that if the PITR retention isn’t set, or is less than the parameter value, then make it (via addOrReplace) the parameter value.

The Assignment

Once you save the definition, use “Assign policy” on the screen that appears

For this, there are several tabs

• Basics:
  • Scope and exclusions let you work on less than the entire subscription
  • enable “policy enforcement”
• Parameters
  • Enter 9 for Min_PITR (to have policy apply 9 days as the minimum)
• Remediation
  • Tick “create remediation task”
  • Default is to use a system managed identity

Then create this assignment

 Initial Remediation

Once the assignment is created, look at the compliance blade to see it; Azure policy is asynchronous, so for a newly created assignment, it takes a little while before it begins checking resources in its scope.

Similarly, “remediation tasks” on the remediation blade shows the task pending to begin with.

Once the initial remediation scan completes, you can look at the backup retention policies (in Data Management | backups) on the logical server(s) and see that the PITR retention periods have been increased to a minimum of 9 days.

Ongoing operation

With the initial remediation complete, the policy will now intercept non-compliant changes, and refactor them on the fly. For example, if we use PowerShell to set the retention to 2 days

$DB_PITR = get-azsqldatabasebackupshorttermretentionpolicy -ResourceGroupName mylittlestarter-rg -ServerName mylittlesql -DatabaseName oppo
$DB_PITR | Set-AzSqlDatabaseBackupShortTermRetentionPolicy -RetentionDays 2


ResourceGroupName         : mylittlestarter-rg
ServerName                : mylittlesql
DatabaseName              : oppo
RetentionDays             : 9
DiffBackupIntervalInHours : 12

The update completes, but the summary shows that the retention stays as 9 days

The experience on the portal is the same; we can change the retention to 1 day in the GUI, and the operation succeeds, but with the retention remaining at 9 days. In the activity log of either the logical server or the database, this shows up as a modify, with the JSON detail of the modify showing the policy name and the effect.

Tricky bits

A few challenges that can cause delays:

• Retention policies are separate resources – Both short-term and long-term backup retention aren’t direct attributes of the database resource. Instead, they exist as their own resources (e.g., with retentionDays) tied to the database.

• Keep policies simple – Focusing each policy on a single resource (like SQL DB PITR) proved more effective than trying to create one large, all-encompassing policy.

• Case sensitivity matters – The policy definition code is case-sensitive, which can easily trip you up if not handled carefully.

• The definitionRoleID is just the GUID of the security role that the policy is going to need, not anything to do with the identity that’s created for the remediation task…but the GUID is potentially different for each subscription, hence the PowerShell to figure out this GUID
• Writing the definitions in PowerShell means that they are just plain-text, and don’t have any syntax helpers; syntax issues in the definition tend to appear as strange “error converting to JSON” messages.
• Waiting patiently for the initial policy remediation cycle to finish; I haven’t found any “make it so” options

References

The posts mentioned in the introduction are

• Automatically Enable LTR and PITR Policy upon a Database creation on Azure SQL Managed Instance | Microsoft Community Hub using audits and runbooks
• Azure custom policy to prevent backup retention period to be below X number - Azure SQL | Microsoft Community Hub which uses ‘Deny’ to fail attempts that don’t meet the requirements.

Expanding this using PowerShell

With a working example for SQL DB PITR, we now want to expand this to have policies that cover both short and long term retention for both SQL DB and SQL MI.

The code below isn’t exhaustive, and being a sample, doesn’t have error checking; note that the code uses “less” for the policy test, but operators like “equals” and “greater” (https://learn.microsoft.com/en-us/azure/governance/policy/concepts/definition-structure-policy-rule#conditions ) are available to build more complex tests, depending on the policy requirements. The document Programmatically create policies - Azure Policy | Microsoft Learn covers using powershell with Azure policy.

Other wrinkles that this sample doesn’t explicitly cater for include

• LTR retentions are held in ISO 8601 format (eg, ‘P8D’ for 8 days), so it’s not trivial to do less than tests; in theory ARM template functions could be used to convert these into the number of days, but this example just does an equality check, and enforces the policy, without any understanding that P4W is a longer period than P20D
• LTR  isn’t available for serverless databases with autopause enabled (https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose#auto-pause ); this would need some form of scope control, potentially either using resource groups, or a more complex test in the policy definition to look at the database attributes
• A few service levels, for example the Basic database SLO, have different limits for their short term retention
• PITR for databases that could be offline (stopped managed instances, auto-paused serverless databases, etc) hasn’t been explicitly tested.
• Remediation tasks just run to completion, with no rescheduling; to ensure that all existing databases are made compliant, this could be expanded to have a loop to check the count of resources needing remediation, and start a task if the relevant existing ones are complete

<# /***This Artifact belongs to the Data SQL Ninja Engineering Team***/

Name:     Enforce_SQL_PaaS_backup_retention.ps1
Author:   Databases SQL CSE/Ninja, Microsoft Corporation
Date:     August 2025
Version:  1.0

Purpose: This is a sample to create the Azure policy defintions, assignment and remediation tasks to enfore organisational policies for minimum short-term (PITR) and weekly long-term (LTR) backup retention.
  
Prerequisities:
- connect to your azure environment using Connect-AzAccount
- Register the resource provider (may already be done in your environment) using Register-AzResourceProvider -ProviderNamespace 'Microsoft.PolicyInsights'
- if needed to modify/update this script, this can be used to find field names:
Get-AzPolicyAlias -ListAvailable -NamespaceMatch 'Microsoft.Sql' | where ResourceType -like '*retentionpol*' | Select-Object -ExpandProperty 'Aliases' | select Name

      
Warranty: This script is provided on as "AS IS" basis and there are no warranties, express or implied, including, but not limited to implied warranties of merchantability or fitness for a particular purpose. USE AT YOUR OWN RISK. 
Feedback: Please provide comments and feedback to the author at datasqlninja@microsoft.com
#>


# parameters to modify

$Location = 'EastUS'        # the region to create the managed identities used by the remediation tasks
$subscriptionID = (Get-AzContext).Subscription.id  # by default use the current Subscription as the scope; change if needed

# the policies to create; PITR can do a less than comparison, but LTR has dates, so uses string equalities
[array]$policies = @()
$policies += @{type = 'DB'; backups='PITR'; name = 'Enforce SQL DB PITR retention'; ParameterName = 'Minimum_PITR'; ParameterValue = 9; Role = 'contributor'; Category='Backup'}
$policies += @{type = 'MI'; backups='PITR'; name = 'Enforce SQL MI PITR retention'; ParameterName = 'Minimum_PITR'; ParameterValue = 9; Role = 'contributor'; Category='Backup'}
# LTR retention is in ISO8601 format, eg P2W = 2 weeks, P70D = 70 days; 'PT0S' = no retention
$policies += @{type = 'DB'; backups='LTR';name = 'Enforce SQL DB LTR retention'; Weekly = 'P4W'; Monthly = 'PT0S'; Yearly = 'PT0S'; WeekofYear = 1; Role = 'contributor'; Category='Backup'}
$policies += @{type = 'MI'; backups='LTR';name = 'Enforce SQL MI LTR retention'; Weekly = 'P4W'; Monthly = 'PT0S'; Yearly = 'PT0S'; WeekofYear = 1; Role = 'contributor'; Category='Backup'}


# templates for the Policy definition code; this has placeholders that are replaced in the loop
$Policy_definition_template_PITR = @'
{
  "mode": "All",
  "policyRule": {
    "if": {
      "anyOf": [
        {
          "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays",
          "exists": false
        },
        {
          "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays",
          "less": "[parameters('<ParameterName>')]"
        }
      ]
    },
    "then": {
      "effect": "modify",
      "details": {
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/<RoleGUID>"
        ],
        "operations": [
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/<Type>/databases/backupShortTermRetentionPolicies/retentionDays",
            "value": "[parameters('<ParameterName>')]"
          }
        ]
      }
    }
  },
  "parameters": {
    "<ParameterName>": {
      "type": "Integer"
    }
  }
}
'@

# LTR, look for any of the weekly/monthly/yearly retention settings not matching
$Policy_definition_template_LTR = @'
{
  "mode": "All",
  "policyRule": {
    "if": {
      "anyOf": [
        {
          "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention",
          "exists": false
        },
        {
          "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention",
          "notEquals": "[parameters('Weekly_retention')]"
        },
        {
          "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/monthlyRetention",
          "notEquals": "[parameters('Monthly_retention')]"
        },
        {
          "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/yearlyRetention",
          "notEquals": "[parameters('Yearly_retention')]"
        }
      ]
    },
    "then": {
      "effect": "modify",
      "details": {
        "roleDefinitionIds": [
          "/providers/Microsoft.Authorization/roleDefinitions/<RoleGUID>"
        ],
        "operations": [
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weeklyRetention",
            "value": "[parameters('Weekly_retention')]"
          },
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/monthlyRetention",
            "value": "[parameters('Monthly_retention')]"
          },
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/yearlyRetention",
            "value": "[parameters('Yearly_retention')]"
          },
          {
            "operation": "addOrReplace",
            "field": "Microsoft.Sql/<Type>/databases/backupLongTermRetentionPolicies/weekOfYear",
            "value": "[parameters('WeekofYear')]"
          }
        ]
      }
    }
  },
  "parameters": {
    "Weekly_retention": {
      "type": "String"
    },
    "Monthly_retention": {
      "type": "String"
    },
    "Yearly_retention": {
      "type": "String"
    },
    "WeekofYear": {
      "type": "Integer"
    }
  }
}
'@

# main loop

foreach ($policy in $policies)
{
    # translate the Role name into its GUID
    $Role = Get-AzRoleDefinition -name $($policy.Role)
    $type = $policy.type -replace 'MI','managedInstances' -replace 'DB','servers'
    $template = if ($policy.backups -eq 'PITR') {$Policy_definition_template_PITR} else {$Policy_definition_template_LTR}
    # generate the definition code for this policy
    $policy_definition = $template -replace '<Type>',$type -replace '<RoleGUID>',$($Role.Id) -replace '<ParameterName>',$policy.ParameterName 

    # create the policy definition
    $PolicyDefinition = new-AzPolicyDefinition -Name $($policy.name) -Policy $policy_definition -Metadata "{'category':'$($policy.Category)'}"
    
    # create the assignment
    if ($policy.backups -eq 'PITR')
        {
            $PolicyParameters = @{$($policy.ParameterName)=($($policy.ParameterValue))}
        }
        else
        {   
            $PolicyParameters = @{"Weekly_retention"=($($policy.Weekly)); "Monthly_retention"=($($policy.Monthly)); "Yearly_retention"=($($policy.Yearly)); "WeekofYear"=($($policy.WeekofYear));}
        }
        $PolicyAssignment = New-AzPolicyAssignment -Name $($policy.name) -PolicyDefinition $PolicyDefinition -PolicyParameterObject $PolicyParameters -IdentityType 'SystemAssigned' -Location $Location

    # now follow the docs page to wait for the ID to be created, and assign the roles required to it; https://learn.microsoft.com/en-us/azure/governance/policy/how-to/remediate-resources?tabs=azure-powershell
    # include a loop to wait until the managed identity created as part of the assignment creation is available
    do
    {
      $ManagedIdentity = Get-AzADServicePrincipal -ObjectId $PolicyAssignment.IdentityPrincipalId -erroraction SilentlyContinue
      if (!($ManagedIdentity)) {start-sleep -Seconds 1} # wait for a bit...
    }
    until ($ManagedIdentity)
    $roleDefinitionIds = $PolicyDefinition.PolicyRule.then.details.roleDefinitionIds
    
    if ($roleDefinitionIds.Count -gt 0)
    {
        $roleDefinitionIds | ForEach-Object {
            $roleDefId = $_.Split("/") | Select-Object -Last 1
            $roleAssigned = New-AzRoleAssignment -ObjectId $PolicyAssignment.IdentityPrincipalId -RoleDefinitionId $roleDefId -Scope "/subscriptions/$($subscriptionID)"
        }
    }

    # lastly create the remediation task
    $RemediationTask = Start-AzPolicyRemediation -Name $($policy.name) -PolicyAssignmentId $PolicyAssignment.Id 
}

# confirm that the policies have been set up
Get-AzPolicyDefinition | where name -In $policies.name | format-table Name, PolicyType
Get-AzPolicyAssignment | where name -In $policies.name | format-table Name, Parameter

 Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com). Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.