Automating Azure SQL Database Restore using Azure CLI

This script can be executed using Windows powershell ISE once Az module in imported using below command:

Script to restore databases present in an elastic pool:

#Connect to the Azure account using login which has owner or contributor role of the subscriptions

Connect-AzAccount

#Set the Subscription ID of source SQL server to a variable

$SourceSubscriptionID = "xxxx-xxxx-xxxx-xxxx-xxxx"

#Set the context to source subscription

Set-AzContext -Subscription $SourceSubscriptionID

#Self explanatory variables

$SourceResourceGroupName = "rg-xxxx-xxxx-qa-db"

# without the FQDN .database.windows.net

$SourceServerName = "tlm-xx-xxxx-qa-azsql"

$SourceElasticPoolName = "elastic-pool-xx-qa"

#Get the list of databases in Elastic pool at source SQL server

$SourceDatabase = Get-AzSqlElasticPoolDatabase -ResourceGroupName $SourceResourceGroupName -ServerName $SourceServerName -ElasticPoolName $SourceElasticPoolName

#Set the Subscription ID of destination SQL server to a variable

$DestinationSubscriptionID = "xxxx-xxxx-xxxx-xxxx-xxxx"

#Set the context to destination subscription

Set-AzContext -Subscription $DestinationSubscriptionID

#Self explanatory variables

$DestinationResourceGroupName = "rg-xxxx-xxx-dev-db"

$DestinationServerName = "xxx-xx-xxxx-dev-azsql"

$DestinationElasticPoolName = "elastic-pool-xx-dev"

#Get the list of databases in Elastic pool at destination SQL server

$DestinationDatabase = Get-AzSqlElasticPoolDatabase -ResourceGroupName $DestinationResourceGroupName -ServerName $DestinationServerName -ElasticPoolName $DestinationElasticPoolName

#Write-Output $SourceDatabase.DatabaseName

#Write-Output $DestinationDatabase.DatabaseName

# Point int time to which destination DBs need to be restored with source copy YYYY-MM-DD

$pointInTime = "2024-01-24T12:00:00.000Z"

#This loop will restore each DB present in destination elastic pool from the source

foreach ($sdb in $SourceDatabase.DatabaseName)

{

foreach ($ddb in $DestinationDatabase.DatabaseName)

{

#This condition will ensure DBs present at destination with same name as source are only restored.

if ($sdb -eq $ddb)

{

#This variable is required to fetch resource ID of source database

$Database = Get-AzSqlDatabase -ResourceGroupName $SourceResourceGroupName -ServerName $SourceServerName -DatabaseName $sdb

#This statement will first delete the destination DBs before restoring

Remove-AzSqlDatabase -ResourceGroupName $DestinationResourceGroupName -ServerName $DestinationServerName -DatabaseName $ddb -Force

Restore-AzSqlDatabase -FromPointInTimeBackup `

-PointInTime $pointInTime `

-ResourceId $Database.ResourceID `

-ServerName $DestinationServerName `

-ResourceGroupName $DestinationResourceGroupName `

-TargetDatabaseName $ddb `

-ElasticPoolName $DestinationElasticPoolName

#break from if loop

break

}

}

}