Step by Step Restore of Dedicated SQL Pool using Geo Backup

Question

Step by Step Restore of Dedicated SQL Pool using Geo Backup

PS 401

All,

I am trying to find some step-by-step documentation of restoring a Geo Backup of dedicated SQL Pool in a different resource group & different region where Synapse is supported. Preferably using Az Cli.

It would be a great help if someone can point me to the appropriate resources.

Thank you!

QuantumCache 20,681 Reputation points Moderator

2023-07-05T15:38:16.9033333+00:00

Hello @PSJust checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
If the response is helpful, please click "Accept Answer" and Click 'Yes'. So that we can close this thread.

Answer accepted by question author

0 additional answers

Your answer

QuantumCache 20,681 Reputation points Moderator

2023-07-05T15:38:16.9033333+00:00

Hello @PSJust checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
If the response is helpful, please click "Accept Answer" and Click 'Yes'. So that we can close this thread.

Answer 1

Luke Murray 11,521 MVP

Hi, PS

Have you seen the PowerShell example here?

Its using: Restore-AzSynapseSqlPool.

$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$WorkspaceName="<YourWorkspaceNameWithoutURLSuffixSeeNote>"  # Without sql.azuresynapse.net
#$TargetResourceGroupName="<YourTargetResourceGroupName>" # uncomment to restore to a different workspace.
#$TargetWorkspaceName="<YourtargetWorkspaceNameWithoutURLSuffixSeeNote>"  
$SQLPoolName="<YourDatabaseName>"
$NewSQLPoolName="<YourDatabaseName>"

Connect-AzAccount
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName

# list all restore points
Get-AzSynapseSqlPoolRestorePoint -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -Name $SQLPoolName
# Pick desired restore point using RestorePointCreationDate "xx/xx/xxxx xx:xx:xx xx"
$PointInTime="<RestorePointCreationDate>"

# Get the specific SQL pool to restore
$SQLPool = Get-AzSynapseSqlPool -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -Name $SQLPoolName
# Transform Synapse SQL pool resource ID to SQL database ID because currently the restore command only accepts the SQL database ID format.
$DatabaseID = $SQLPool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
    -replace "workspaces", "servers" `
    -replace "sqlPools", "databases"

# Restore database from a restore point
$RestoredDatabase = Restore-AzSynapseSqlPool –FromRestorePoint -RestorePoint $PointInTime -ResourceGroupName $SQLPool.ResourceGroupName `
    -WorkspaceName $SQLPool.WorkspaceName -TargetSqlPoolName $NewSQLPoolName –ResourceId $DatabaseID -PerformanceLevel DW100c

# Use the following command to restore to a different workspace
#$TargetResourceGroupName = $SQLPool.ResourceGroupName # for restoring to different workspace in same resourcegroup 
#$RestoredDatabase = Restore-AzSynapseSqlPool –FromRestorePoint -RestorePoint $PointInTime -ResourceGroupName $TargetResourceGroupName `
#    -WorkspaceName $TargetWorkspaceName -TargetSqlPoolName $NewSQLPoolName –ResourceId $DatabaseID -PerformanceLevel DW100c

# Verify the status of restored database
$RestoredDatabase.status

For the Azure CLI - there's: az synapse sql pool restore (https://learn.microsoft.com/cli/azure/synapse/sql/pool?view=azure-cli-latest&WT.mc_id=AZ-MVP-5004796#az-synapse-sql-pool-restore)

PS 401 Reputation points

2023-07-05T20:16:25.8+00:00

Thank you @Luke Murray , will try the Az cli version.
PS 401 Reputation points

2023-07-08T23:52:21.1266667+00:00

Hi @Luke Murray

I have couple of follow-up questions.

I need to prepare the BCDR plan for the Dedicated SQL Pool(formerly SQL DW). This pool is not part of the Synapse workspace yet on my end. So, this does not have a Synapse workspace name unless if I am missing something.

#1. I have already setup a Synapse pool in the secondary region and hoping to restore the Geo-Backup. However, it's specified that I can only access Geo-Backup if I am unable to access the User-defined restore points in my primary region and I cannot use my Automatic restore points to restore in the secondary region.

for testing my DR, this doesn't make any sense.

#2. The Az Cli you shared is referring to Synapse but not Dedicated SQL Pool(formerly SQL DW). We will have the Workspace name only if we make our Dedicated SQL Pool(formerly SQL DW) as part of Synapse workspace.

and I doubt if I can use these commands to restore the Geo-backup.

Please share ideas if any to test my BCDR Plan.

Thank you!
Luke Murray 11,521 Reputation points MVP

2023-07-09T02:43:40.0266667+00:00

Try this: Geo-restore a dedicated SQL pool in Azure Synapse Analytics

For DR testing, you may have to create a once-off backup, then restore that. Some Azure features will do an automatic failure of the Azure load balancer/DNS, etc, to flick the second region into existing automatically on their end; the same is true for Azure storage accounts, although you can read from a secondary region storage endpoint - it doesn't look like this is supported for Azure Synapse Analytics, so doing the manual backup and restore will get you most of the way there, to confirm it at least works or not.
PS 401 Reputation points

2023-07-09T17:07:43.73+00:00

Thank you @Luke Murray . will post my update here.
PS 401 Reputation points

2023-07-09T18:47:33.9533333+00:00

Hi @Luke Murray , upon trying the solution you have suggested...below is my outcome. at this point am unsure, If I am doing something wrong?

I am sure, the variable values(Subscription, RG, Servers) I have supplied all exists & it complains about the parent resource does not exist. I am hope full that it's not complaining about the privileges here.

Please advise. Thank you!

PS 401

here are the actual error msgs.

#1. 

Get-AzSqlDatabase: Failed to perform 'read' on resource(s) of type 'servers/databases', because the parent resource '/subscriptions/XXXXX/resourceGroups/XXXXX/providers/Microsoft.Sql/servers/XXXXX' could not be found.

#2.

Get-AzSqlDatabaseGeoBackup: ParentResourceNotFound: Failed to perform 'read' on resource(s) of type 'servers/recoverableDatabases', because the parent resource '/subscriptions/XXXXX/resourceGroups/XXXXX/providers/Microsoft.Sql/servers/XXXXX' could not be found.

PS 401 Reputation points

2023-07-09T23:03:37.9+00:00

I was able to do the test restore the Geo-Backup onto the newly created server in the secondary region using the Powershell script outlined in this article.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-restore-from-geo-backup

Thank you so much @Luke Murray for the help all the while.

Share via

Step by Step Restore of Dedicated SQL Pool using Geo Backup

0 additional answers

Your answer