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

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...