Configuring backups retention for all Azure SLQ Managed instances under same subscription
Published Sep 28 2023 01:03 PM 1,949 Views

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  




Version history
Last update:
‎Sep 28 2023 01:01 PM
Updated by: