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.

Dedicate SQL Pool

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

User-Defined Restore Points

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

Restore point retention

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.

Snapshot retention when a SQL pool is dropped

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

Geo-backups

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.

Serverless SQL Pool

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.

Alternative:

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.