Restore a dedicated SQL pool from a deleted workspace
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. To get started, see Install Azure PowerShell. 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
Open PowerShell
Connect to your Azure account.
Set the context to the subscription that contains the workspace that was dropped.
Specify the approximate datetime the workspace was dropped.
Construct the resource ID for the database you wish to recover from the dropped workspace.
Restore the database from the dropped workspace
Verify the status of the recovered database as 'online'.
$SubscriptionID="<YourSubscriptionID>" $ResourceGroupName="<YourResourceGroupName>" $WorkspaceName="<YourWorkspaceNameWithoutURLSuffixSeeNote>" # Without sql.azuresynapse.net $DatabaseName="<YourDatabaseName>" $TargetResourceGroupName="<YourTargetResourceGroupName>" $TargetWorkspaceName="<YourtargetServerNameWithoutURLSuffixSeeNote>" $TargetDatabaseName="<YourDatabaseName>" Connect-AzAccount Set-AzContext -SubscriptionID $SubscriptionID # Define the approximate point in time the workspace was dropped as DroppedDateTime "yyyy-MM-ddThh:mm:ssZ" (ex. 2022-01-01T16:15:00Z) $PointInTime="<DroppedDateTime>" $DroppedDateTime = Get-Date -Date $PointInTime # construct the resource ID of the sql pool you wish to recover. The format required 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. $RestoredDatabase = Restore-AzSynapseSqlPool -FromDroppedSqlPool -DeletionDate $DroppedDateTime -TargetSqlPoolName $TargetDatabaseName -ResourceGroupName $TargetResourceGroupName -WorkspaceName $TargetWorkspaceName -ResourceId $SourceDatabaseID # 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
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for