Geo-restore a dedicated SQL pool in Azure Synapse Analytics

  • Article

In this article, you learn to restore your dedicated SQL pool (formerly SQL DW) from a geo-backup through Azure portal and PowerShell.

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.

Verify your DTU capacity. Each dedicated SQL pool (formerly SQL DW) is hosted by a logical SQL server (for example, myserver.database.chinacloudapi.cn) which has a default DTU quota. Verify that the SQL server has enough remaining DTU quota for the database being restored.

Restore from an Azure geographical region through PowerShell

To restore from a geo-backup, use the Get-AzSqlDatabaseGeoBackup and Restore-AzSqlDatabase cmdlet.

Note

You can perform a geo-restore to Gen2! To do so, specify an Gen2 ServiceObjectiveName (e.g. DW1000c) as an optional parameter.

  1. Before you begin, make sure to install Azure PowerShell.
  2. Open PowerShell.
  3. Connect to your Azure account and list all the subscriptions associated with your account.
  4. Select the subscription that contains the data warehouse to be restored.
  5. Get the data warehouse you want to recover.
  6. Create the recovery request for the data warehouse.
  7. Verify the status of the geo-restored data warehouse.
  8. To configure your data warehouse after the restore has completed, see Configure your database after recovery.
$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>"  # Without database.chinacloudapi.cn
$TargetResourceGroupName="<YourTargetResourceGroupName>" # Restore to a different server.
$TargetServerName="<YourtargetServerNameWithoutURLSuffixSeeNote>"  
$DatabaseName="<YourDatabaseName>"
$NewDatabaseName="<YourDatabaseName>"
$TargetServiceObjective="<YourTargetServiceObjective-DWXXXc>"

Connect-AzAccount -Environment AzureChinaCloud
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName
Get-AzSqlDatabase -ServerName $ServerName -ResourceGroupName $ResourceGroupName

# Get the data warehouse you want to recover
$GeoBackup = Get-AzSqlDatabaseGeoBackup -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName

# Recover data warehouse
$GeoRestoredDatabase = Restore-AzSqlDatabase –FromGeoBackup -ResourceGroupName $TargetResourceGroupName -ServerName $TargetServerName -TargetDatabaseName $NewDatabaseName –ResourceId $GeoBackup.ResourceID -ServiceObjectiveName $TargetServiceObjective

# Verify that the geo-restored data warehouse is online
$GeoRestoredDatabase.status

The recovered database will be TDE-enabled if the source database is TDE-enabled.

Restore from an Azure geographical region through Azure portal

Follow the steps outlined below to restore a dedicated SQL pool (formerly SQL DW) from a geo-backup:

  1. Sign in to your Azure portal account.

  2. Search for Dedicated SQL pools (formerly SQL DW).

    New DW 2

  3. Click add and fill out the information requested in the Basics tab and click Next: Additional settings.

    Basics

  4. For Use existing data parameter, select Backup and select the appropriate backup from the scroll down options. Click Review + Create.

    backup

  5. Once the data warehouse has been restored, check that the Status is Online.

Next Steps