Restore an Azure SQL Database After Deletion of the SQL Server

Aim: To restore a Long-Term Retention (LTR) backup of an Azure SQL Database after the associated SQL Server has been deleted.

Microsoft’s documentation states that you can restore a backed up Azure SQL Database in the event that you have deleted its SQL Server. Specifically, it states “If you delete a server, all of its databases and their PITR backups are also deleted. You can’t restore a deleted server, and you can’t restore the deleted databases from PITR backups. If you had configured LTR backups for those database, you can use those backups to restore the databases to a different server.”

Unfortunately, it doesn’t tell you how to restore that LTR backup when its SQL Server has been deleted. At this time, it’s not possible to restore it (or even find it) in the Azure Portal. It has to be restored using a different method, like PowerShell, although this is not obvious from the official documentation. Thanks Microsoft!

All of the following came from Atul Gaikwad’s extremely helpful post on MSSQLTips. I’m just making it into a concise guide for my purposes.

In preparation, I have created an Azure SQL Database (DatabaseToBeRestored) on a SQL Server (thisserverwillbedeleted).

I confirm that the database has at least one LTR backup available for restore.

And then, I delete the SQL Server.

Now that the prep is complete, I need to find the backup using Get-AzSqlDatabaseLongTermRetentionBackup. To do so, I run the following in the Cloud Shell in the Azure Portal.

Get-AzSqlDatabaseLongTermRetentionBackup -Location northeurope -ServerName "thisserverwillbedeleted" -DatabaseName "DatabaseToBeRestored"

The Location parameter needs to be set to the region of the of the SQL Server that was deleted, ServerName needs to be set to the name of the SQL Server that was deleted, and DatabaseName needs to be set to the name of the Database I want to restore.

If you do not know the SQL Server and Database names, you can use the following to get all the LTR backups for deleted databases in a region.

Get-AzSqlDatabaseLongTermRetentionBackup -Location northeurope -DatabaseState Deleted

Note that if you try to use the ResourceGroupName parameter with Get-AzSqlDatabaseLongTermRetentionBackup in this circumstance, you will not get any results as the Server deletion has decoupled the backups from the Resource Group.

Now that the backup has been located, I confirm that I have another SQL Server (sqlserverforrestore) available. Then, I run the following to restore the LTR backup to the new SQL Server using Restore-AzSqlDatabase.

$DeletedDatabase = Get-AzSqlDatabaseLongTermRetentionBackup -Location northeurope -ServerName "thisserverwillbedeleted" -DatabaseName "DatabaseToBeRestored" -OnlyLatestPerDatabase
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceGroupName "SQL-DB-testing" -ServerName "sqlserverforrestore" -TargetDatabaseName "RestoredDatabase" -ResourceId $DeletedDatabase.ResourceID

For Restore-AzSqlDatabase, the ServerName parameter must be the new SQL Server (i.e. not the one that was deleted), ResourceGroupName needs to be set to the Resource Group containing TargetServerName (this can be set to $DeletedDatabase.ResourceGroupName if it’s the same resource group as the original SQL Server), and TargetDatabaseName needs to be set to the new name that I want the Database to be restored to.

And I wait and wait (because this is not a fast process even on a tiny database) and eventually the Cloud Shell times out (because it does that after about 20 minutes). But the backups is still restoring in the background and once it’s done, the database is visible in the Azure Portal. Success!

One thing to note is that the original backup frequencies and retention policies are not applied to the restored database so those will need to be set up again.