This article is contributed. See the original author and article here.

I made this as a quick lab based on the public documentation to help address this doubt about restoring a deleted database on Azure.

This is a common customer doubt.


  • Create a new DW database and change the performance level to the minimum, this is just a lab as you can check on fig. 1.


Fig 1 Create database


  • Connect on SQL Server Management Studio and check the last backup by running the following:


select * from sys.pdw_loader_backup_runs



Fig. 2 No backups


It is completely empty as there is no backup for this database, yet. So, let’s force a backup to be done by creating a restore point.


  • Define a new restore point on the Azure Portal by clicking on… New Restore Point Fig 3.


Fig 3 Restore Point


  • Connect on SQL Server Management Studio again and check the lastest backups by running again pdw_loader_backup_run as you can confirm on fig 4:


Fig 4 One backup


By the docs: 

A data warehouse restore is a new data warehouse that is created from a restore point of an existing or deleted data warehouse. Restoring your data warehouse is an essential part of any business continuity and disaster recovery strategy because it re-creates your data after accidental corruption or deletion.



SQL DW will create 42 restore points and the user can additionally create more 42 restore points. Basically this is a restore to a point in time.


  • Let’s add one more restore point.
  • Create a table like the script below and then add a new restore point. You should get the results as shown in Fig 5.


create table test1 ( i int)




Fig 5 New restore point.


  • Drop the database from  Azure Portal as fig 6.


Fig 6 Drop the database


  • Restore  the database:


Public doc:


  1. Open PowerShell.
  2. Connect to your Azure account and list all the subscriptions associated with your account.
  3. Select the subscription that contains the deleted SQL pool to be restored.
  4. Get the specific deleted data warehouse.

Add the details and run on power shell:




$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without
#$TargetResourceGroupName="<YourTargetResourceGroupName>" # uncomment to restore to a different server.

Select-AzSubscription -SubscriptionName $SubscriptionName

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

# Restore deleted database
$RestoredDatabase = Restore-AzSqlDatabase –FromDeletedDatabaseBackup –DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $DeletedDatabase.ServerName -TargetDatabaseName $NewDatabaseName –ResourceId $DeletedDatabase.ResourceID

# Use the following command to restore deleted data warehouse to a different server
#$RestoredDatabase = Restore-AzSqlDatabase –FromDeletedDatabaseBackup –DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $TargetResourceGroupName -ServerName $TargetServerName -TargetDatabaseName $NewDatabaseName –ResourceId $DeletedDatabase.ResourceID

# Verify the status of restored database





Simple like that! You can also use the portal for it, look at the public documentation for more details.

  • If you run again sys.pdw_loader_backup_runs after the restore you will see the restore points as the user drop database and the latest backups:


Fig 7 Restore points


Note: A dropped DW database is cleaned-up  8 days after the drop and cannot be restored


That is it!



UK Engineer.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: