Script to restore all the deleted databases from an Azure SQL Server

[Ahmed_S_Mahmoud]

Purpose: 

This article helps you restore many deleted databases from Azure SQL Server. Although there is PowerShell command  to restore the database but it cannot restore all the databases at once, in this article we provide a handy script to generate the PowerShell commands for all deleted databases.

Step-by-Step Walkthrough:

• Open a new cloud shell window from the top right side of azure portal or you may use PowerShell to connect with your Azure subscription. 

• Make sure you connected to PowerShell on cloud shell window. 

• Paste the below PowerShell script and execute it on the Cloud Shell,  it will generate the restore Powershell commands for each database with status validity.  

Note:- Make sure to update the resource group and server name parameters to your own environment.

# Update the following parameters based on your environment
$ResourceGroupName="XXXXXXXXXXXXXXXXX" # Change the resource group name
$ServerName="XXXXXXXXXXXXXXX"  # Change the server Name Without database.windows.net

# Get the deleted databases to restore
$DeletedDatabase = Get-AzSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroupName -ServerName $ServerName

# Generate the Restore commands for the deleted databases
For ($i=0; $i -le $DeletedDatabase.length-1; $i++) {

Write-Host -ForegroundColor Green "##############################################################################################"
Write-Host -ForegroundColor Green "##########################################" $DeletedDatabase[$i].DatabaseName "########################################" 

$1 = "$" +"RestoredDatabase = Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate " + "'"  + $DeletedDatabase[$i].DeletionDate + "'"
$2 = " -ResourceGroupName " + "'" + $DeletedDatabase[$i].ResourceGroupName + "'"
$3 = " -ServerName " + "'" + $DeletedDatabase[$i].ServerName + "'"
$4 = " -TargetDatabaseName " + "'" + $DeletedDatabase[$i].DatabaseName + "'"
$5 = " -ResourceId " + "'" +$DeletedDatabase[$i].ResourceID+ "'"
$1 + $2 + $3 + $4 + $5

# Generate the database verify command
Write-Host -ForegroundColor Green "# Verify the status of restored database" 
Write-Host '$RestoredDatabase.status'
} 

Sample Output:

##############################################################################################
########################################## database01 ########################################
$RestoredDatabase = Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate '01/29/2021 10:01:58' -ResourceGroupName 'XXXXXXXXXXXXXXXX'
 -ServerName 'deletedserver01' -TargetDatabaseName 'database01' -ResourceId '/subscriptions/XXXXXXXXXXXXXXXX/resourceGroups/XXXXXXXXXXXXXXXX/providers/Microsoft.Sql/servers/deletedserver01/restorableDroppedDatabases/database01,132563881187970000'
# Verify the status of restored database
$RestoredDatabase.status
##############################################################################################
########################################## database02 ########################################
$RestoredDatabase = Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate '01/29/2021 10:01:59' -ResourceGroupName 'XXXXXXXXXXXXXXXX'
 -ServerName 'deletedserver01' -TargetDatabaseName 'database02' -ResourceId '/subscriptions/XXXXXXXXXXXXXXXX/resourceGroups/XXXXXXXXXXXXXXXX/providers/Microsoft.Sql/servers/deletedserver01/restorableDroppedDatabases/database02,132563881191870000'
# Verify the status of restored database
$RestoredDatabase.status
##############################################################################################
########################################## database03 ########################################
$RestoredDatabase = Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate '01/29/2021 10:01:59' -ResourceGroupName 'XXXXXXXXXXXXXXXX'
 -ServerName 'deletedserver01' -TargetDatabaseName 'database03' -ResourceId '/subscriptions/XXXXXXXXXXXXXXXX/resourceGroups/XXXXXXXXXXXXXXXX/providers/Microsoft.Sql/servers/deletedserver01/restorableDroppedDatabases/database03,132563881191400000'
# Verify the status of restored database
$RestoredDatabase.status
##############################################################################################
########################################## database04 ########################################
$RestoredDatabase = Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate '01/29/2021 10:01:58' -ResourceGroupName 'XXXXXXXXXXXXXXXX'
 -ServerName 'deletedserver01' -TargetDatabaseName 'database04' -ResourceId '/subscriptions/XXXXXXXXXXXXXXXX/resourceGroups/XXXXXXXXXXXXXXXX/providers/Microsoft.Sql/servers/deletedserver01/restorableDroppedDatabases/database04,132563881184500000'
# Verify the status of restored database
$RestoredDatabase.status

• Use the generated output commands to restore your databases and validate each database availability after restoration complete, you may filter out the unwanted databases by adding '#' at the front of the restore command .   

## in below sample, I have restored one of the databases 'database04' as an example:

Another useful script to restore a single database to an earlier point in time PowerShell: Restore an automatic backup of a database in SQL Database - Azure SQL Database | Microso...

I hope you find this article helpful.

Thanks to colleague Suhas for helping in developing the powershell script.

If you have any feedback please do not hesitate to provide it in the comment section below.

Ahmed S. Mazrouh