How to use backup and restore in Azure Synapse Dedicated SQL pool. Use dedicated SQL pool restore points to recover or copy your data warehouse to a previous state in the primary region. Use data warehouse geo-redundant backups to restore to a different geographical region.
What is a data warehouse snapshot
A data warehouse snapshot creates a restore point you can leverage to recover or copy your data warehouse to a previous state.
Dedicated SQL pool Recovery Time Objective (RTO) rates can vary. Factors that may affect the recovery (restore) time:
- The database size
- The location of the source and target data warehouse (i.e., geo-restore)
Automatic Restore Points
Snapshots are a built-in feature that creates restore points. You do not have to enable this capability. However, the dedicated SQL pool should be in an active state for restore point creation.
To see when the last snapshot started, run this query on your online dedicated SQL pool.
select top 1 *
from sys.pdw_loader_backup_runs
order by run_id desc
;Restore point retention
The following lists details for restore point retention periods:
- 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 (including both user-defined and automatic).
- Snapshots are not taken when a dedicated SQL pool is paused.
- The age of a restore point is measured by the absolute calendar days from the time the restore point is taken including when the SQL pool is paused.
- At any point in time, a dedicated SQL pool is guaranteed to be able to store up to 42 user-defined restore points or 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)
