Forum Discussion

Petr_VM's avatar
Petr_VM
Copper Contributor
Jul 14, 2020

Set Default PITR on Azure SQL Server Level

Hi All,

 

Is it possible to set up default Point-in-time Recovery on the Azure SQL server level so that every newly created database gets this setting? Now it is 7 by default and can be changes only per database after it is created.

 

Best Regards,

Petr

azure sql
  • MrCoups's avatar
    MrCoups
    Copper Contributor
    Aug 10, 2020

    Petr_VM You can't set this at a server level, but you can create a custom Azure Policy to check for the desired value and enforce it if it's not set.

     

    The policy below will look for the desired value and report if it's not set correctly. Change the effect to "modify" if you want it to automatically change the value to your requirements. Also change 4 to what ever you want the maximum value to be, it will audit for less than or equal to that value.

     

     

    {
    "policyRule": {
        "if": {
            "allOf": [{
                    "field": "type",
                    "equals": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies"
                },
                {
                    "field": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies/retentionDays",
                    "lessOrEquals": 4
                }
            ]
        },
        "then": {
            "effect": "audit"
        }
    }
}

     

Resources