Restore a dedicated SQL pool from a deleted workspace

  • Article

In this article, you learn how to restore a dedicated SQL pool in Azure Synapse Analytics after an accidental drop of a workspace using PowerShell.

Note

This guidance is for dedicated SQL pools in Azure Synapse workspaces only. For standalone dedicated SQL pools (formerly SQL DW), follow guidance Restore sql pool from deleted server.

Before you begin

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. See Install Azure PowerShell to get started. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Restore the SQL pool from the dropped workspace

The following sample script accomplishes these steps:

  1. Open PowerShell

  2. Connect to your Azure account.

  3. Set the context to the subscription that contains the workspace that was dropped.

  4. Determine the datetime the workspace was dropped. This step retrieves the exact date and time the workspace SQL pool was dropped.

    • This step assumes that the workspace with the same name resource group and same values is still available.
    • If not, recreate the dropped workspace with the same workspace name, resource group name, region, and all the same values from prior dropped workspace.

  5. Construct a string the resource ID of the sql pool you wish to recover. The format requires Microsoft.Sql. This includes the date and time when the server was dropped.

  6. Restore the database from the dropped workspace. Restore to the target workspace with the source SQL pool.

  7. Verify the status of the recovered database as 'online'.

    $SubscriptionID = "<YourSubscriptionID>"
$ResourceGroupName = "<YourResourceGroupName>"
$WorkspaceName = "<YourWorkspaceNameWithoutURLSuffixSeeNote>"  # Without sql.azuresynapse.azure.cn
$DatabaseName = "<YourDatabaseName>"
$TargetResourceGroupName = "<YourTargetResourceGroupName>"
$TargetWorkspaceName = "<YourtargetServerNameWithoutURLSuffixSeeNote>"
$TargetDatabaseName = "<YourDatabaseName>"

Connect-AzAccount -Environment AzureChinaCloud
Set-AzContext -SubscriptionID $SubscriptionID

# Get the exact date and time the workspace SQL pool was dropped.
# This assumes that the workspace with the same name resource group and same values is still available.
# If not, recreate the dropped workspace with the same workspace name, resource group name, region, 
# and all the same values from prior dropped workspace.
# There should only be one selection to select from.
$paramsGetDroppedSqlPool = @{
    ResourceGroupName = $ResourceGroupName
    WorkspaceName     = $WorkspaceName
    Name              = $DatabaseName
}
$DroppedDateTime = Get-AzSynapseDroppedSqlPool @paramsGetDroppedSqlPool `
    | Select-Object -ExpandProperty DeletionDate

# Construct a string of the resource ID of the sql pool you wish to recover.
# The format requires Microsoft.Sql. This includes the approximate date time the server was dropped.
$SourceDatabaseID = "/subscriptions/$SubscriptionID/resourceGroups/$ResourceGroupName/providers/" `
                + "Microsoft.Sql/servers/$WorkspaceName/databases/$DatabaseName"    

# Restore to the target workspace with the source SQL pool.
$paramsRestoreSqlPool = @{
    FromDroppedSqlPool  = $true
    DeletionDate        = $DroppedDateTime
    TargetSqlPoolName   = $TargetDatabaseName
    ResourceGroupName   = $TargetResourceGroupName
    WorkspaceName       = $TargetWorkspaceName
    ResourceId          = $SourceDatabaseID
}
$RestoredDatabase = Restore-AzSynapseSqlPool @paramsRestoreSqlPool

# Verify the status of restored database
$RestoredDatabase.status

Troubleshoot

If "An unexpected error occurred while processing the request." message is received, the original database might not have any recovery points available due to the original workspace being short lived. Typically this is when the workspace existed for less than one hour.

Related content