How to restore a dropped Dedicated SQL Pool using powershell
Published Oct 28 2022 04:05 AM 760 Views
Microsoft

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

 

1.png

 

And zero Restore points available for it:

Get-AzSynapseSqlPoolRestorePoint (Az.Synapse) | Microsoft Learn

 

2.png

 

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

 

3.png

 

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

[-FromDroppedSqlPool]. 

5.png

 

 

4.png

 

Here the code 

 

 

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

Connect-AzAccount
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 database.windows.net
$DatabaseName="your Dedicated Sql pool name" #the dropped one
$TargetServerName="target server name"  
$TargetDatabaseName="target Dedicated SQL Pool"

Connect-AzAccount
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

 

6.png

 

That's all.

Luca

 

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