Configuring backups retention for all Azure SLQ Managed instances under same subscription

  • Thread starter Thread starter Palomag_MSFT
  • Start date Start date
P

Palomag_MSFT

Backup retention is part if business continuity and disaster recovery strategy.

You can configure backup retention using Azure portal , Azure CLI, PowerShell and Res API.



For instructions on changing automated backup settings, you can refer to the following resources:

Change automated backup settings - Azure SQL Managed Instance | Microsoft Learn

Long-term backup retention - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn



To help you on this task here you have sample script that will assist you in adjusting the Point-in-Time Recovery (PITR) and Long-Term Retention (LTR) settings for all Managed Instance Databases under your subscription.










# Disclaimer:
# This script is provided for example purposes only and is not intended for production use without proper review.
# The author and owner of the script assume no responsibility for any damage or loss that may arise from the use of this script.
# Before using this script in a production environment, it is strongly recommended to conduct thorough testing and make any necessary adaptations.

# Your code starts here

$RetentionDays = 30

$WeeklyRetention = 0

$MonthlyRetention =12

$YearlyRetention =5

$WeekOfYear =1



#Set backup configuration retention for Azure MI databases

$AzureSQLMIS = Get-AzResource | Where-Object ResourceType -EQ Microsoft.Sql/managedInstances



foreach ($AzureSQLMI in $AzureSQLMIS){

[string]$instancename = $AzureSQLMI.Name
[string]$resourcename = $AzureSQLMI.ResourceGroupName


$AzureSQLServerDataBases = Get-AzSqlInstanceDatabase -InstanceName $instancename -ResourceGroupName $resourcename | Where-Object Name -NE “master”


foreach ($AzureSQLServerDataBase in $AzureSQLServerDataBases) {

#Short Term Retention Policy
Set-AzSqlInstanceDatabaseBackupShortTermRetentionPolicy -ResourceGroupName $resourcename -InstanceName $instancename -DatabaseName $($AzureSQLServerDataBase.Name) -RetentionDays $RetentionDays
#Long Term Retention Policy
Set-AzSqlInstanceDatabaseBackupLongTermRetentionPolicy -WeeklyRetention "P$($WeeklyRetention)W" -MonthlyRetention "P$($MonthlyRetention)M" -YearlyRetention "P$($YearlyRetention)Y" -WeekOfYear $WeekOfYear -InstanceName $instancename -DatabaseName $($AzureSQLServerDataBase.Name) -ResourceGroupName $resourcename
}
}

Continue reading...
 
Back
Top