Share via

IaaS SQL Server backup policy ARM

Dan 176 Reputation points
2020-07-13T14:27:25.153+00:00

Hi,

I am trying to create a backup vault and policies via ARM. I have the vault and VM policy deployed but I am struggling with the SQL policy. I currently have daily and log backup enabled, but I am struggling with weekly, monthly and yearly.

The template I am using is below: 

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Recovery Services Vault"
      }
    },
    "policyName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Backup Policy"
      }
    },
    "sqlpolicyName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Backup Policy"
      }
    },
    "scheduleRunTimes": {
      "type": "array",
      "defaultValue": [
        "03:00"
      ],
      "metadata": {
        "description": "Times in day when backup should be triggered. e.g. 01:00 or 13:00. Must be an array, however for IaaS VMs only one value is valid. This will be used in LTR too for daily, weekly, monthly and yearly backup."
      }
    },
    "timeZone": {
      "type": "string",
      "defaultValue": "UTC",
      "metadata": {
        "description": "Any Valid timezone, for example:UTC, Pacific Standard Time. Refer: https://msdn.microsoft.com/en-us/library/gg154758.aspx"
      }
    },
    "instantRpRetentionRangeInDays": {
      "type": "int",
      "defaultValue": 5,
      "allowedValues": [
        1,
        2,
        3,
        4,
        5
      ],
      "metadata": {
        "description": "Number of days Instant Recovery Point should be retained"
      }
    },
    "dailyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of days you want to retain the backup"
      }
    },
    "daysOfTheWeek": {
      "type": "array",
      "metadata": {
        "description": "Backup will run on array of Days like, Monday, Tuesday etc. Applies in Weekly retention only."
      }
    },
    "weeklyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of weeks you want to retain the backup"
      }
    },
    "monthlyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of months you want to retain the backup"
      }
    },
    "daysOfTheWeekForMontlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Days for Monthly Retention (Min One or Max all values from scheduleRunDays, but not any other days which are not part of scheduleRunDays)"
      }
    },
    "weeksOfTheMonthForMonthlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Weeks for Monthly Retention - First, Second, Third, Fourth, Last"
      }
    },
    "monthsOfYear": {
      "type": "array",
      "metadata": {
        "description": "Array of Months for Yearly Retention"
      }
    },
    "daysOfTheWeekForYearlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Days for Yearly Retention (Min One or Max all values from scheduleRunDays, but not any other days which are not part of scheduleRunDays)"
      }
    },
    "weeksOfTheMonthForYearlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Weeks for Yearly Retention - First, Second, Third, Fourth, Last"
      }
    },
    "yearlyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of years you want to retain the backup"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.RecoveryServices/vaults",
      "apiVersion": "2015-11-10",
      "name": "[parameters('vaultName')]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "RS0",
        "tier": "Standard"
      },
      "properties": {}
    },
    {
      "apiVersion": "2016-06-01",
      "name": "[concat(parameters('vaultName'), '/', parameters('policyName'))]",
      "type": "Microsoft.RecoveryServices/vaults/backupPolicies",
      "dependsOn": [
        "[concat('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
      ],
      "location": "[parameters('location')]",
      "properties": {
        "backupManagementType": "AzureIaasVM",
        "instantRpRetentionRangeInDays": "[parameters('instantRpRetentionRangeInDays')]",
        "schedulePolicy": {
          "scheduleRunFrequency": "Daily",
          "scheduleRunDays": null,
          "scheduleRunTimes": "[parameters('scheduleRunTimes')]",
          "schedulePolicyType": "SimpleSchedulePolicy"
        },
        "retentionPolicy": {
          "dailySchedule": {
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('dailyRetentionDurationCount')]",
              "durationType": "Days"
            }
          },
          "weeklySchedule": {
            "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('weeklyRetentionDurationCount')]",
              "durationType": "Weeks"
            }
          },
          "monthlySchedule": {
            "retentionScheduleFormatType": "Weekly",
            "retentionScheduleDaily": {
              "daysOfTheMonth": [
                {
                  "date": 1,
                  "isLast": false
                }
              ]
            },
            "retentionScheduleWeekly": {
              "daysOfTheWeek": "[parameters('daysOfTheWeekForMontlyRetention')]",
              "weeksOfTheMonth": "[parameters('weeksOfTheMonthForMonthlyRetention')]"
            },
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('monthlyRetentionDurationCount')]",
              "durationType": "Months"
            }
          },
          "yearlySchedule": {
            "retentionScheduleFormatType": "Weekly",
            "monthsOfYear": "[parameters('monthsOfYear')]",
            "retentionScheduleDaily": {
              "daysOfTheMonth": [
                {
                  "date": 1,
                  "isLast": false
                }
              ]
            },
            "retentionScheduleWeekly": {
              "daysOfTheWeek": "[parameters('daysOfTheWeekForYearlyRetention')]",
              "weeksOfTheMonth": "[parameters('weeksOfTheMonthForYearlyRetention')]"
            },
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('yearlyRetentionDurationCount')]",
              "durationType": "Years"
            }
          },
          "retentionPolicyType": "LongTermRetentionPolicy"
        },
        "timeZone": "[parameters('timeZone')]"
      }
    },
    {
      "type": "Microsoft.RecoveryServices/vaults/backupPolicies",
      "apiVersion": "2018-01-10",
      "name": "[concat(parameters('vaultName'), '/', parameters('sqlpolicyName'))]",
      "dependsOn": [
        "[resourceId('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
      ],
      "properties": {
        "backupManagementType": "AzureWorkload",
        "workloadType": "SQLDataBase",
        "settings": {
          "timeZone": "UTC",
          "issqlcompression": false,
          "isCompression": true
        },
        "subProtectionPolicy": [
          {
            "policyType": "Full",
            "schedulePolicy": {
              "schedulePolicyType": "SimpleSchedulePolicy",
              "scheduleRunFrequency": "Daily",
              "scheduleRunTimes": [
                "2019-03-24T16:00:00Z"
              ],
              "scheduleWeeklyFrequency": 0
            },
            "retentionPolicy": {
              "retentionPolicyType": "LongTermRetentionPolicy",
              "dailySchedule": {
                "retentionTimes": [
                  "2019-03-24T16:00:00Z"
                ],
                "retentionDuration": {
                  "count": 30,
                  "durationType": "Days"
                }
              },
              "weeklySchedule": {
                "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
                "retentionTimes": "2019-03-24T16:00:00Z",
                "retentionDuration": {
                  "count": "[parameters('weeklyRetentionDurationCount')]"
                }
              }
            }
          },
          {
            "policyType": "Log",
            "schedulePolicy": {
              "schedulePolicyType": "LogSchedulePolicy",
              "scheduleFrequencyInMins": 60
            },
            "retentionPolicy": {
              "retentionPolicyType": "SimpleRetentionPolicy",
              "retentionDuration": {
                "count": 30,
                "durationType": "Days"
              }
            }
          }
        ]
      }
    }
  ]
}

The section that I have added but isn't working is: 

                  "weeklySchedule": {
                    "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
                    "retentionTimes": "2019-03-24T16:00:00Z",
                    "retentionDuration": {
                      "count": "[parameters('weeklyRetentionDurationCount')]"
                    }
                  }

I am just getting an error stating "Status Message: Input for create or update policy is not in proper format
Please check format of parameters like schedule time, schedule days, retention time and retention days (Code:BMSUserErrorInvalidPolicyInput)"

I'm struggling to find any reference for this section to even guide me in the correct direction.

Thanks

Azure Backup
Azure Backup
An Azure backup service that provides built-in management at scale.
1,283 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. SwathiDhanwada-MSFT 18,771 Reputation points
    2020-07-16T13:56:20.98+00:00

    @probi Here is the entire template for your reference.

    {
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "vaultName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Recovery Services Vault"
      }
    },
    "policyName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Backup Policy"
      }
    },
    "sqlpolicyName": {
      "type": "string",
      "metadata": {
        "description": "Name of the Backup Policy"
      }
    },
    "scheduleRunTimes": {
      "type": "array",
      "defaultValue": [
        "03:00"
      ],
      "metadata": {
        "description": "Times in day when backup should be triggered. e.g. 01:00 or 13:00. Must be an array, however for IaaS VMs only one value is valid. This will be used in LTR too for daily, weekly, monthly and yearly backup."
      }
    },
    "timeZone": {
      "type": "string",
      "defaultValue": "UTC",
      "metadata": {
        "description": "Any Valid timezone, for example:UTC, Pacific Standard Time. Refer: https://msdn.microsoft.com/en-us/library/gg154758.aspx"
      }
    },
    "instantRpRetentionRangeInDays": {
      "type": "int",
      "defaultValue": 5,
      "allowedValues": [
        1,
        2,
        3,
        4,
        5
      ],
      "metadata": {
        "description": "Number of days Instant Recovery Point should be retained"
      }
    },
    "dailyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of days you want to retain the backup"
      }
    },
    "daysOfTheWeek": {
      "type": "array",
      "metadata": {
        "description": "Backup will run on array of Days like, Monday, Tuesday etc. Applies in Weekly retention only."
      }
    },
    "weeklyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of weeks you want to retain the backup"
      }
    },
    "monthlyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of months you want to retain the backup"
      }
    },
    "daysOfTheWeekForMontlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Days for Monthly Retention (Min One or Max all values from scheduleRunDays, but not any other days which are not part of scheduleRunDays)"
      }
    },
    "weeksOfTheMonthForMonthlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Weeks for Monthly Retention - First, Second, Third, Fourth, Last"
      }
    },
    "monthsOfYear": {
      "type": "array",
      "metadata": {
        "description": "Array of Months for Yearly Retention"
      }
    },
    "daysOfTheWeekForYearlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Days for Yearly Retention (Min One or Max all values from scheduleRunDays, but not any other days which are not part of scheduleRunDays)"
      }
    },
    "weeksOfTheMonthForYearlyRetention": {
      "type": "array",
      "metadata": {
        "description": "Array of Weeks for Yearly Retention - First, Second, Third, Fourth, Last"
      }
    },
    "yearlyRetentionDurationCount": {
      "type": "int",
      "metadata": {
        "description": "Number of years you want to retain the backup"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.RecoveryServices/vaults",
      "apiVersion": "2015-11-10",
      "name": "[parameters('vaultName')]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "RS0",
        "tier": "Standard"
      },
      "properties": {}
    },
    {
      "apiVersion": "2016-06-01",
      "name": "[concat(parameters('vaultName'), '/', parameters('policyName'))]",
      "type": "Microsoft.RecoveryServices/vaults/backupPolicies",
      "dependsOn": [
        "[concat('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
      ],
      "location": "[parameters('location')]",
      "properties": {
        "backupManagementType": "AzureIaasVM",
        "instantRpRetentionRangeInDays": "[parameters('instantRpRetentionRangeInDays')]",
        "schedulePolicy": {
          "scheduleRunFrequency": "Daily",
          "scheduleRunDays": null,
          "scheduleRunTimes": "[parameters('scheduleRunTimes')]",
          "schedulePolicyType": "SimpleSchedulePolicy"
        },
        "retentionPolicy": {
          "dailySchedule": {
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('dailyRetentionDurationCount')]",
              "durationType": "Days"
            }
          },
          "weeklySchedule": {
            "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('weeklyRetentionDurationCount')]",
              "durationType": "Weeks"
            }
          },
          "monthlySchedule": {
            "retentionScheduleFormatType": "Weekly",
            "retentionScheduleDaily": {
              "daysOfTheMonth": [
                {
                  "date": 1,
                  "isLast": false
                }
              ]
            },
            "retentionScheduleWeekly": {
              "daysOfTheWeek": "[parameters('daysOfTheWeekForMontlyRetention')]",
              "weeksOfTheMonth": "[parameters('weeksOfTheMonthForMonthlyRetention')]"
            },
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('monthlyRetentionDurationCount')]",
              "durationType": "Months"
            }
          },
          "yearlySchedule": {
            "retentionScheduleFormatType": "Weekly",
            "monthsOfYear": "[parameters('monthsOfYear')]",
            "retentionScheduleDaily": {
              "daysOfTheMonth": [
                {
                  "date": 1,
                  "isLast": false
                }
              ]
            },
            "retentionScheduleWeekly": {
              "daysOfTheWeek": "[parameters('daysOfTheWeekForYearlyRetention')]",
              "weeksOfTheMonth": "[parameters('weeksOfTheMonthForYearlyRetention')]"
            },
            "retentionTimes": "[parameters('scheduleRunTimes')]",
            "retentionDuration": {
              "count": "[parameters('yearlyRetentionDurationCount')]",
              "durationType": "Years"
            }
          },
          "retentionPolicyType": "LongTermRetentionPolicy"
        },
        "timeZone": "[parameters('timeZone')]"
      }
    },
    {
      "type": "Microsoft.RecoveryServices/vaults/backupPolicies",
      "apiVersion": "2018-01-10",
      "name": "[concat(parameters('vaultName'), '/', parameters('sqlpolicyName'))]",
      "dependsOn": [
        "[resourceId('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
      ],
      "properties": {
        "backupManagementType": "AzureWorkload",
        "workloadType": "SQLDataBase",
        "settings": {
          "timeZone": "UTC",
          "issqlcompression": false,
          "isCompression": true
        },
        "subProtectionPolicy": [
          {
            "policyType": "Full",
            "schedulePolicy": {
              "schedulePolicyType": "SimpleSchedulePolicy",
              "scheduleRunFrequency": "Daily",
              "scheduleRunTimes": [
                "2019-03-24T16:00:00Z"
              ],
              "scheduleWeeklyFrequency": 0
            },
            "retentionPolicy": {
              "retentionPolicyType": "LongTermRetentionPolicy",
              "dailySchedule": {
                "retentionTimes": [
                  "2019-03-24T16:00:00Z"
                ],
                "retentionDuration": {
                  "count": 30,
                  "durationType": "Days"
                }
              },
              "weeklySchedule": {
                "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
                "retentionTimes": [ "2019-03-24T16:00:00Z" ],
                "retentionDuration": {
                  "count": "[parameters('weeklyRetentionDurationCount')]",
                  "durationType" : "Weeks"
                }
              }
            }
          },
          {
            "policyType": "Log",
            "schedulePolicy": {
              "schedulePolicyType": "LogSchedulePolicy",
              "scheduleFrequencyInMins": 60
            },
            "retentionPolicy": {
              "retentionPolicyType": "SimpleRetentionPolicy",
              "retentionDuration": {
                "count": 30,
                "durationType": "Days"
              }
            }
          }
        ]
      }
    }
  ]
}

    You just need to modify your weekly schedule as shown below. The changes I have made are

    • keep the retentiontimes value in square brackets
    • Added durationType property to weekly schedule "weeklySchedule": {
      "daysOfTheWeek": "[parameters('daysOfTheWeek')]",
      "retentionTimes": [ "2019-03-24T16:00:00Z" ],
      "retentionDuration": {
      "count": "[parameters('weeklyRetentionDurationCount')]",
      "durationType": "Weeks"
      }
      }

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.