Microsoft

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.
     
    new_database.png

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

 

nobackups.png

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.

restorepoint.png

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:

onebackup.png

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.

Doc: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/backup-and-restore

 

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)

 

 

newrestorepoint.png

Fig 5 New restore point.

 

  • Drop the database from  Azure Portal as fig 6.

drop.png

Fig 6 Drop the database

 

  • Restore  the database:

 

Public doc: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-resto...

 

  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:

 

 

 

$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without database.windows.net
#$TargetResourceGroupName="<YourTargetResourceGroupName>" # uncomment to restore to a different server.
#$TargetServerName="<YourtargetServerNameWithoutURLSuffixSeeNote>"
$DatabaseName="<YourDatabaseName>"
$NewDatabaseName="<YourDatabaseName>"

Connect-AzAccount
Get-AzSubscription
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
$RestoredDatabase.status

 

 

 

 

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:

restorepoints_later.png

Fig 7 Restore points

 

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

 

That is it!

 

Liliam 

UK Engineer.