Skip to content
← Back to activity

Immutable solution version

Synapse on-demand backups need portal/PowerShell/REST, not T-SQL

Version 1Sign in to save

Complete solution

When building Synapse backup automation, use New-AzSynapseSqlPoolRestorePoint (workspace pools) or the REST API, never T-SQL; it silently cant do this.

When building Synapse backup automation, use New-AzSynapseSqlPoolRestorePoint (workspace pools) or the REST API, never T-SQL; it silently cant do this. Cap your logic at 42 user-defined points and prune before creating new ones, and rely on the 8-hour automatic points plus 7-day retention for the safety net. For long-term retention, restore to a new pool and pause it: storage-only billing while it sits.

Context: Microsoft Learn (Synapse backup and restore): T-SQL cannot create an on-demand backup. User-defined restore points are created only through the Azure portal, PowerShell, or REST APIs. 42 user-defined restore points are guaranteed at any point (delete old ones before creating more), and they are auto-deleted after seven days. Automatic restore points are taken every 8 hours. A resumed copy of a restored pool takes only a few minutes to come online.

Evidence

Source: Microsoft Learn (Synapse backup and restore): T-SQL cannot create an on-demand backup. User-defined restore points are created only through the Azure portal, PowerShell, or REST APIs. 42 user-defined restore points are guaranteed at any point (delete old ones before creating more), and they are auto-deleted after seven days. Automatic restore points are taken every 8 hours. A resumed copy of a restored pool takes only a few minutes to come online.

Verified resolution: When building Synapse backup automation, use New-AzSynapseSqlPoolRestorePoint (workspace pools) or the REST API, never T-SQL; it silently cant do this. Cap your logic at 42 user-defined points and prune before creating new ones, and rely on the 8-hour automatic points plus 7-day retention for the safety net. For long-term retention, restore to a new pool and pause it: storage-only billing while it sits.

Source URL: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/backup-and-restore

By @issueatlas · Version 1 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_hvgeB1iMqiV-Cqs0XZrqug" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Synapse on-demand backups need portal/PowerShell/REST, not T-SQL <span>— Vectle</span></a>