How to restore a dropped Dedicated SQL Pool using powershell
Published Oct 28 2022 04:05 AM 1,687 Views

What a restore point is ?
A restore point is basically a snapshot of your database you can use to restore/copy your Dedicated SQL Pool (DW) to a previous state.
It consists in a set of files located in Azure Storage and it captures changes to your DW.
Database-restore points for Azure Synapse Analytics - Cloud Adoption Framework | Microsoft Learn


There are 2 types of Restore point:

  1. Automatic
  2. Used-Defined

Backup and restore - snapshots, geo-redundant - Azure Synapse Analytics | Microsoft Learn


Automatic Restore points

When your Dedicated Sql Pool is active automatic Restore points are taken to ensure 8 hours RPO. Automatic Restore Points cannot be deleted, and their retention period is 7 days and cannot be changed.  If you pause it frequently, automatic restore points may not be created so make sure to create user-defined restore point before pausing the dedicated SQL pool.


User-Defined Restore point

You can manually trigger the creation of a Restore point whenever you want, and you can have up to 42 User-Defined Restore points. Their retention is 7 days (cannot be changed) and are automatically deleted on your behalf

If you require restore points longer than 7 days, please vote for this capability here.


What if I drop the Dedicated SQL Pool ?

If you drop it when active, a final snapshot is created and saved for seven days. You can restore the dedicated SQL pool to the final restore point created at deletion. If the dedicated SQL pool is dropped in a paused state, no snapshot is taken. In this scenario, make sure to create a user-defined restore point before dropping the dedicated SQL pool.


Let's see how it works


In my Synapse workspace I have a dedicated pool and it's active




And zero Restore points available for it:

Get-AzSynapseSqlPoolRestorePoint (Az.Synapse) | Microsoft Learn




Since it is active a final automatic snapshot will be taken just after the drop request
 Get-AzSynapseDroppedSqlPool (Az.Synapse) | Microsoft Learn




Get-AzSynapseSqlPoolRestorePoint now failed since I dropped the Dedicated SQL Pool. To identify the only available restore point, I used the Get-AzSynapseDroppedSqlPool cmd.

To proceed with the recover, we will use the Restore-AzSynapseSqlPool (Az.Synapse) | Microsoft Learn cmd using the option







Here the code 



$SubscriptionID="your Subscription Id"
$ResourceGroupName="your resource group name"
$WorkspaceName="your workspace name"  # Without
$DatabaseName="your dedicated sql pool" #the dropped one
$TargetWorkspaceName="target workspace"  
$TargetDatabaseName="target dedicated sql pool "

Set-AzContext -Subscription $SubscriptionID

#Getting the snapshot automatically created during while dropping the Dedicated SQL Pool
$dropped = Get-AzSynapseDroppedSqlPool -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -Name $DatabaseName

#Properly formatting the ResourceId that will be passed to the restore cmd.
$dbid = "/subscriptions/"+$SubscriptionID+"/resourceGroups/"+$ResourceGroupName+"/providers/Microsoft.Sql/servers/"+$WorkspaceName+"/databases/"+$DatabaseName
#Saving the target workspace as an object that will be passed to the restore cmd	
$workspaceDestination = get-AzSynapseworkspace -name $TargetWorkspaceName	

#Execute the restore
Restore-AzSynapseSqlPool -FromDroppedSqlPool -WorkspaceObject $workspaceDestination -Name $TargetDatabaseName -ResourceId $dbid -DeletionDate $dropped.deletiondate



You can use the same approach with Dedicated SQL Pool, formerly Azure SQL DW, just need to slightly change the code to use specific powershell commands: Restore a deleted dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics | Microsoft Learn

Here the code:


$SubscriptionID="your subscription id"
$ResourceGroupName="your resource group"
$ServerName="your server name"  # Without
$DatabaseName="your Dedicated Sql pool name" #the dropped one
$TargetServerName="target server name"  
$TargetDatabaseName="target Dedicated SQL Pool"

Set-AzContext -Subscription $SubscriptionID

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

# Restore deleted database
Restore-AzSqlDatabase -FromDeletedDatabaseBackup -DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $TargetServerName -TargetDatabaseName $TargetDatabaseName -ResourceId $DeletedDatabase.ResourceID



and its results




That's all.



Version history
Last update:
‎Oct 28 2022 03:32 AM
Updated by: