Automating Azure SQL Managed Instance restore using Azure-CLI

Connect-AzAccount #Store Source DB instance subscription ID in a variable. Please note this should be subscription ID and NOT the subscription name $SourceSubscriptionID = "xxxx-xxxx-xxxx-xxxx-xxxx"

#Set the AZ context to source subscription Set-AzContext -Subscription $SourceSubscriptionID

#Store Source DB instance in a variable, DO NOT use FQDN. Remove the suffix .73495ah*****.database.windows.net $SourceInstanceName = “xxxxxx”

#Store Source resource group name in a variable $SourceResourceGroupName = “rg-xxxxx”

#Store source DB names list in a variable $sourceDatabase = Get-AzSqlInstanceDatabase -InstanceName $SourceInstanceName -ResourceGroupName $SourceResourceGroupName

#Store Destination DB instance subscription ID in a variable. Please note this should be subscription ID and NOT the subscription name $DestinationSubscriptionID = “xxxx-xxxx-xxxx-xxxx-xxxx”

#Set the AZ context to Destination subscription Set-AzContext -Subscription $DestinationSubscriptionID

#Store Source resource group name in a variable $DestinationResourceGroupName = “xxxxx”

#Store Source DB instance in a variable, DO NOT use FQDN. Remove the suffix .73495ah*****.database.windows.net $DestinationInstanceName = “xxxx”

#Store Destination DB names list in a variable $DestDatabase = Get-AzSqlInstanceDatabase -InstanceName $DestinationInstanceName -ResourceGroupName $DestinationResourceGroupName

#Write-Output $DestDatabase.name #Write-Output $sourceDatabase.name

# Point int time to which destination DBs need to be restored with source copy YYYY-MM-DD $pointInTime = “2023-10-16T12:00:00.000Z”

#This loop will restore each DB present in destination instance from the source foreach ($sdb in $sourceDatabase.Name) { foreach ($ddb in $DestDatabase.Name) { if ($sdb -eq $ddb) { Remove-AzSqlInstanceDatabase -Name $sdb -InstanceName $DestinationInstanceName -ResourceGroupName $DestinationResourceGroupName -Force

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup -SubscriptionId $SourceSubscriptionID -ResourceGroupName $SourceResourceGroupName -InstanceName $SourceInstanceName -Name $sdb -PointInTime $pointInTime -TargetInstanceDatabaseName $sdb -TargetInstanceName $DestinationInstanceName -TargetResourceGroupName $DestinationResourceGroupName ` -TargetSubscriptionId $DestinationSubscriptionID

#break from the if loop break

} } }

#Store if any extra DBs present at source SQL MI which also needs to be copied over to destination SQL MI $ExtraDB = @(‘db_1’,‘db_2’,‘db_3’,‘db_4’)

For ($i=0; $i -lt $ExtraDB.Length; $i++) { #Write-Output $ExtraDB[$i]

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup -SubscriptionId $SourceSubscriptionID -ResourceGroupName $SourceResourceGroupName -InstanceName $SourceInstanceName -Name $ExtraDB[$i] -PointInTime $pointInTime -TargetInstanceDatabaseName $ExtraDB[$i] -TargetInstanceName $DestinationInstanceName -TargetResourceGroupName $DestinationResourceGroupName ` -TargetSubscriptionId $DestinationSubscriptionID

}