Blog Post

Azure Database Support Blog
2 MIN READ

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

Palomag_MSFT's avatar
Palomag_MSFT
Icon for Microsoft rankMicrosoft
Sep 28, 2023

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

 

 

 

Updated Sep 28, 2023
Version 1.0
No CommentsBe the first to comment