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:
- Automatic
- 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
[-FromDroppedSqlPool].
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
That's all.
Luca