Set Default PITR on Azure SQL Server Level

%3CLINGO-SUB%20id%3D%22lingo-sub-1520346%22%20slang%3D%22en-US%22%3ESet%20Default%20PITR%20on%20Azure%20SQL%20Server%20Level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1520346%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20set%20up%20default%20Point-in-time%20Recovery%20on%20the%20Azure%20SQL%20server%20level%20so%20that%20every%20newly%20created%20database%20gets%20this%20setting%3F%20Now%20it%20is%207%20by%20default%20and%20can%20be%20changes%20only%20per%20database%20after%20it%20is%20created.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%2C%3C%2FP%3E%3CP%3EPetr%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1520346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20sql%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

1 Reply

@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"
        }
    }
}