Edit

Share via

Backup, restore, and recovery guidance for Azure Synapse dedicated SQL pools

Applies to:   Azure Synapse Analytics

The dedicated SQL pool (formerly SQL DW) backup and restore component provides an essential safeguard for protecting critical data stored in your SQL pools. Aspects of backup and restore include recovery time objective (RTO), geo-backup, and disaster recovery strategy. Use the following sections, which include frequently asked questions (FAQs), to learn about dedicated SQL pool backups, restores, geo-backups, and so on.

Important prerequisites

  • Dedicated SQL pool should be in an active state for restore point creation. If it's paused frequently, automatic restore points may not be created. Make sure to create a user-defined restore point before pausing the dedicated SQL pool.
  • If you're planning to use a PowerShell script to restore the pool, make sure that you're using the latest version of the following packages:
  • Review the main differences between Azure Synapse (formerly SQL DW) and an Azure Synapse Analytics workspace. If you're planning to restore your pool between those resources, see the difference between Azure Synapse (formerly SQL DW) and an Azure Synapse Analytics workspace.

Backup FAQs

FAQ Solution/Recommendation
Is it possible to take a local backup and store it on-premises from the dedicated SQL pool? Generating a backup file from an existing restore point isn't supported for dedicated SQL pools. If you want to keep a specific restore point, restore that restore point to a different server or database. After you've restored it, you have the dedicated SQL pool online and can pause it indefinitely to save compute costs.
We have a major deployment. Can we perform a backup before that happens? Yes. A user-defined restore point can be used for that purpose. For more information about creating the restore point from either the Azure portal or by using PowerShell, see:
- Use the Azure portal.
- Use PowerShell.

Note: There are guaranteed to be 42 user-defined restore points at any point in time, so they must be dropped before creating another restore point.
Is a long-term backup supported? Long-term backup isn't supported in a dedicated SQL pool. If you want to implement a scenario similar to a long-term backup, follow these steps:
  1. Create a new user-defined restore point, or use one of the automatically generated restore points.
  2. Restore from the newly created restore point to a new data warehouse.
  3. After the restore, you have the dedicated SQL pool online. Pause it indefinitely to save compute costs. The paused database incurs storage charges at the Azure Synapse storage rate.

Restore FAQs

FAQ Solution/Recommendation
What options do I have to restore my dedicated SQL pool? - Restore an existing dedicated SQL pool through the Synapse Studio
- Restore an existing dedicated SQL pool through the Azure portal
- Restore an existing dedicated SQL pool through PowerShell
- Restore an existing dedicated SQL pool to a different subscription through PowerShell
What is the recovery time objective (RTO) to recover the SQL pool? Dedicated SQL pool restore rates can vary depending on the database size and the location of the source and target data warehouses.
I want to restore a backup that's encrypted by a customer-managed key. How can I do this? To restore a backup encrypted with a transparent data encryption (TDE) protector from a key vault (customer-managed key), make sure that the key material is available to the target server by providing the following permissions:
- WrapKey
- UnwrapKey
- Get

If the target server is a dedicated SQL pool, you can link other keys for use by the restore service without marking them as TDE protectors. For more information, see Database backup and restore with customer-managed TDE.
I accidentally dropped the server/workspace, but haven't yet re-created a new one with the same name. How do I recover my server/workspace? - Restore a dedicated SQL pool from a deleted server.
- Restore a dedicated SQL pool from a deleted workspace.
- If the deleted server/workspace is encrypted with a customer-managed key and there are databases with a non-dedicated SQL pool edition, for example, an Azure database, contact the Microsoft support team to recover the server.
I accidentally dropped one of the tables inside my dedicated SQL pool. Can I restore that table alone? What is the fastest way to restore that table? Table-level restore isn't supported in dedicated SQL pools. You can only recover the entire dedicated SQL pool from your backup, and then copy the required table(s) by using:
- Extract, transform, and load (ETL) tool activities, such as copy activity.
- Export and import.
   * Export the data from the restored backup into your data lake by using CREATE EXTERNAL TABLE AS SELECT (CETAS).
   * Import the data by using COPY or PolyBase.
As part of our data management tasks, we might need to support requirements to restore a specific schema or table from 14 days ago. How can we do this? A dedicated SQL pool takes snapshots of your data throughout the day, creating restore points that are available for seven days.

Note: Dedicated SQL pools should be in an active state for restore point creation. Table-level restore isn't supported in dedicated SQL pools.

To support requirements to restore a specific schema or table from 14 days ago, you can export the required table(s) to blob storage by using the native CETAS or any ETL tool. After the restore, you can import the data by using COPY, PolyBase, or any ETL tool.
Does a dedicated SQL pool support a partial restore? A partial restore isn't supported in dedicated SQL pools.
Does a dedicated SQL pool support a table-level restore? Synapse Analytics dedicated SQL pools don't currently support table-level restores. You can recover only an entire database from your backup.
How do I restore a dedicated SQL pool between subscriptions? You can restore an existing dedicated SQL pool to a different subscription through PowerShell. For detailed instructions, see the following guidance:
- Synapse Cross-Subscription Restore - Tech Community
- Restore an existing dedicated SQL pool (formerly SQL DW) to a different subscription through PowerShell
- Restore an existing dedicated SQL pool to a different subscription through PowerShell

Note: When you perform a cross-subscription restore, a Synapse workspace-dedicated SQL pool can be restored only to a standalone dedicated SQL pool.

If the destination that you want is a Synapse workspace:
 1. Create a restore point for the newly created data warehouse.
 2. Use Select -Last 1 to retrieve the last created restore point.
 3. Perform the restore to the Synapse workspace that you want.

Geo-backup FAQs

FAQ Solution/Recommendation
Can I determine the frequency of the geo-backup? No. The frequency of the geo-backup is predetermined.
With geo-back enabled, how many copies of backup are stored in the Azure-paired regions? A geo-backup is created once per day to a paired data center. The recovery point objective (RPO) for a geo-restore is 24 hours.

For more information about geo-backup, see Geo-backups and disaster recovery.
Is it possible to replicate a user-defined snapshot from a primary region to a secondary region? Snapshots (both user-defined and automatic) aren't replicated. Geo-backup is created once every 24 hours from the latest local snapshot. There are no public APIs for replicating a restore point to another region. You can still restore the required backup and pause the instance to save the compute cost.
What are the minimum permissions required to view geo-backup details from the Azure portal? For a dedicated SQL pool (formerly SQL Data Warehouse), if the user needs to interact with the Azure portal to retrieve details of the most recent geo-backup, you need to grant the Reader role at the SQL pool level along with Write permissions. However, if you don't want to provide elevated access with Write permissions to a specific user, you can still grant the Reader role at the SQL pool level. In this case, the user can use the REST API via PowerShell to retrieve a list of geo-backups. For more information, see Geo Backup Policies - REST API.

In a dedicated SQL pool within a Synapse workspace, you only need to assign the Reader role at the SQL pool level for the user to view geo-backups using the Azure portal.

Disaster recovery, recovery point objective, and recovery time objective FAQs

FAQ Solution/Recommendation
In case of disaster recovery, what is the expected recovery point objective (RPO) and recovery time objective (RTO) to recover the dedicated SQL pool? Dedicated SQL pool performs a geo-backup once a day to a paired data center. For a geo-restore, the RPO is 24 hours. If you're looking for a shorter RPO, you can either restore the system restore-point or create a new user-defined restore point and restore it to a different region, based on the required RPO/RTO. After you've restored it, you have the dedicated SQL pool online and can pause it indefinitely to save compute costs.

Keep in mind that:
- You can restore the geo-backup to a server in any other paired region where dedicated SQL pool is supported.
- A geo-backup helps to ensure that you can restore the dedicated SQL pool, which can be helpful if you're unable to access the restore points in your primary region.
- The dedicated SQL pool size may affect the RPO/RTO targets. For example, if you're looking for X-hour(s) RPO/RTO, you can create a user-defined restore. Wait a few minutes, and then restore the backup to the target region. You can pause the restored instance to save compute costs. If the database size is small, the backup can complete under X hour(s). However, as the database grows, X-hour(s) RPO/RTO may not be possible.
What is the recovery time objective (RTO) to recover the dedicated SQL pool? Dedicated SQL pool restore rates can vary depending on the database size and the location of the source and target data warehouses.
Are there best practices that can help me find a good resilience and recovery strategy? What about cross-subscription restore? Individual users and organizations have different goals and different data sizes. For example, if you have a small SQL pool instance, you might use cross-region restore from a local point and then pause it to save the compute cost. For large SQL pool instances, this approach might not work because a cross-region restore may take several days.

For cross-subscription restore options, see:
- Restore an existing dedicated SQL pool to different subscription
- Synapse Cross-Subscription Restore - Tech Community

Solutions to common backup and restore issues

Issue Solution
You recently renamed your Synapse database and you received the following error when trying to recover a geo-backup:
- "No Geo-Backup has been created yet" (through the portal).
- "NotFound" (through PowerShell).		 This issue may occur because the rename hasn't been replicated to your regional pair. You can use the old name to recover your Synapse database.
You want to restore a geo-backup, but you're unable to find a restore point. This issue is mostly likely due to frequent pauses of your dedicated SQL pool. Restore points may not have a chance to complete if the dedicated SQL pool is paused frequently.
You receive an error message "The server is not in a ready state." Check the status of the destination SQL Server to make sure that it's in an Available state. You can see the status in the Overview pane of the destination SQL Server in the Azure portal.
You're moving or restoring your dedicated SQL pool across subscriptions. - Go through the checklist before moving.
- If you need to restore to a new dedicated SQL pool across subscriptions, see Move your Azure Data Warehouse to a new region and/or subscription.
- If you're restoring the dedicated SQL pool to a different server, follow this checklist to ensure the recovered dedicated SQL pool has an appropriate security configuration.
- To remap a database user to a different sign-in, use ALTER USER <UserName> WITH LOGIN = <LoginName>.

Resources