Azure Synapse SQL Pool Backup
November 2, 2021
A data warehouse snapshot creates a restore point you can leverage to recover or copy your data warehouse to a previous state. Since a dedicated SQL pool is a distributed system, a data warehouse snapshot consists of many files that are located in Azure storage. Snapshots capture incremental changes from the data stored in your data warehouse.
Data warehouse restore
A data warehouse restore is a new data warehouse that is created from a restore point of an existing or deleted data warehouse. Restoring your data warehouse is an essential part of any business continuity and disaster recovery strategy because it re-creates your data after accidental corruption or deletion. The data warehouse is also a powerful mechanism to create copies of your data warehouse for test or development purposes.
Automatic Restore Points
- Enable by default
- The dedicated SQL pool should be in an active state and not paused
- Available for 7 days
- The dedicated SQL pool supports an eight-hour recovery point objective (RPO)
- You can restore your data warehouse in the primary region from any one of the snapshots taken in the past seven days
- Automatic restore points currently cannot be deleted by users as the service uses these restore points to maintain SLAs for recovery
- These are manually triggered snapshots to create restore points of your data warehouse before and after large modifications user PowerShell or the Azure portal
- User-defined restore points are available for seven days and are automatically deleted on your behalf
- 42 user-defined restore points are guaranteed at any point in time so they must be deleted before creating another restore point
- Dedicated SQL pool deletes a restore point when it hits the 7-day retention period and when there are at least 42 total restore points
- At any point in time, a dedicated SQL pool is guaranteed to be able to store up to 42 user-defined restore points and 42 automatic restore points as long as these restore points have not reached the 7-day retention period
- If a snapshot is taken, the dedicated SQL pool is then paused for greater than 7 days, and then resumed, the restore point will persist until there are 42 total restore points (including both user-defined and automatic). These will get deleted as new automatic restore points are created after unpausing.
- When you drop a dedicated SQL pool, a final snapshot is created and saved for seven days
- If the dedicated SQL pool is dropped in a paused state, no snapshot is taken
- In that scenario, make sure to create a user-defined restore point before dropping the dedicated SQL pool
- If you delete the server/workspace hosting a dedicated SQL pool, all databases that belong to the server/workspace are also deleted and cannot be recovered. You cannot restore a deleted server
A geo-backup is created once per day to a paired data center. The RPO for a geo-restore is 24 hours. You can restore the geo-backup to a server in any other region where a dedicated SQL pool is supported. A geo-backup ensures you can restore the data warehouse in case you cannot access the restore points in your primary region.
Synapse Analytics serverless SQL pool endpoint can have multiple databases. Serverless SQL pool has no local storage; only metadata objects are stored in databases.
Currently, it is not possible to backup Serverless SQL Pool databases.
- In this blog, where you can see how you can use PowerShell DbaTools to script Synapse SQL objects which may help you.
- Since it’s all metadata, keeping all DDL scripts in version control would work for most scenarios.