Changing backup policy for Azure SQL databases in Azure recovery service valut

Question

How to change backup policy for Azure SQL Database backup configured in RSV. I know one way but with that, policy has been changed on each database level..
Is there any way we can change for all the databases at one go.

Answer 1

Hello @Chetan Jain ,

Thank you for reaching out to the Microsoft Q&A platform. Happy to answer your question. We don't have a way to change the policy at one go for all workload items(DBs) from azure portal. Reference: https://learn.microsoft.com/en-us/azure/backup/manage-monitor-sql-database-backup#modify-policy

I have tried testing through Powershell and below script did the job.

$vault = Get-AzRecoveryServicesVault -Name <VaultName> -ResourceGroupName <RG Name of the Vault>
$pols = Get-AzRecoveryServicesBackupProtectionPolicy -VaultId $vault.ID -WorkloadType MSSQL -BackupManagementType AzureWorkload
$targetPolicy = Get-AzRecoveryServicesBackupProtectionPolicy -VaultId $vault.ID -Name <Target Policy Name>

$containers = Get-AzRecoveryServicesBackupContainer -VaultId $vault.ID -ContainerType AzureVMAppContainer
$bkpItems = Get-AzRecoveryServicesBackupItem -WorkloadType MSSQL -VaultId $vault.ID -Container $container
foreach($container in $containers) {
$bkpItems = Get-AzRecoveryServicesBackupItem -WorkloadType MSSQL -VaultId $vault.ID -Container $container
foreach($bkpItem in $bkpItems) {
Enable-AzRecoveryServicesBackupProtection -Item $bkpItem -Policy $targetPolicy -VaultId $vault.ID
}
}

Note: It would be taking time to complete reconfiguring all the items since there is no parallelism However, at least it would not require the users to make changes manually for each and every DB.
Edit:
And please note that this one will fetch all the protected servers configured with SQL backups in the vault and will change the policy for all the SQL instances in each of those servers..

So, if the user wants to change the policy for DBs in a selected server, then the user would have to edit the script and replace the line starting with

$containers = with the below information:$containers = Get-AzRecoveryServicesBackupContainer -VaultId $vault.ID -ContainerType AzureVMAppContainer | ?{$_.Name -eq "VMAppContainer;Compute;<RG Name of the SQL VM>;<VM Name>"}

Reference: https://learn.microsoft.com/en-us/azure/backup/backup-azure-vms-automation#change-policy-for-backup-items

Please "Accept as Answer" and Upvote if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Answer 2

You can use se Set-AzureRmRecoveryServicesBackupProtectionPolicy cmdlet in PowerShell to update all policies.

$recoveryServicesVaults = Get-AzureRmRecoveryServicesVault

foreach($recoveryServicesVault in $recoveryServicesVaults)

{

     Set-AzureRmRecoveryServicesVaultContext -Vault $recoveryServicesVault

     $backupPolicies=Get-AzureRmRecoveryServicesBackupProtectionPolicy -WarningAction Ignore

     foreach($backupPolicy in $backupPolicies)
     {
         $schPol = Get-AzureRmRecoveryServicesBackupSchedulePolicyObject -WorkloadType "AzureVM"
         $retPol = Get-AzureRmRecoveryServicesBackupRetentionPolicyObject -WorkloadType "AzureVM"

         $retPol.IsWeeklyScheduleEnabled  = $true
         $retPol.IsMonthlyScheduleEnabled = $true
         $retPol.IsYearlyScheduleEnabled =  $true
         $retPol.DailySchedule.DurationCountInDays = 90
         $retpol.WeeklySchedule.DurationCountInWeeks = 10
         $retpol.MonthlySchedule.DurationCountInMonths = 4
         $retpol.YearlySchedule.DurationCountInYears = 1
         Set-AzureRmRecoveryServicesBackupProtectionPolicy -Policy $backupPolicy -SchedulePolicy $SchPol -RetentionPolicy $RetPol
      }
}