Engine behaviour measured 2026-08-29 and 2026-09-03 against an engine reporting
SERVERPROPERTY('EngineEdition') 5, Edition SQL Azure, ProductVersion 12.0.2000.8. Every command line was taken on 2026-09-03 from
the help output of az 2.90.0 and SqlPackage 170.4.83.3 on this machine; the scope and retention
limits come from the Microsoft Learn pages in References. The restores need a real logical
server and were not run, so resource names are placeholders.
The correction
Asked to recover an Azure SQL Database, an agent trained on the boxed engine reaches for
RESTORE DATABASE ... WITH REPLACE, BACKUP DATABASE, RESTORE HEADERONLY or
ALTER DATABASE ... SET RECOVERY. None of that surface exists here. Learn is blunt: you recover
using the portal, PowerShell, the Azure CLI or the REST API, and you can't use Transact-SQL. The
second wrong belief compounds the first. Learn again: you can't overwrite an existing database
during restore. Every restore creates a new database under the name you pass, and moving it to
where the application connects is a step you perform yourself. Nothing warns you if you skip it,
so the failure surfaces later as a connection error blamed on the application.
1. There is no restore T-SQL, measured
| Written from habit | What happens here |
|---|---|
RESTORE DATABASE db FROM DISK = '...' WITH REPLACE |
Msg 40510, not supported |
BACKUP DATABASE db TO DISK = '...' or TO URL = '...' |
Msg 40510, not supported |
BACKUP LOG db TO DISK = '...' |
Msg 40510, not supported |
RESTORE HEADERONLY / FILELISTONLY / VERIFYONLY |
Msg 40510, and the text names RESTORE VOLUME |
EXEC sys.sp_get_database_backup_policy |
Msg 2812, could not find stored procedure |
ALTER DATABASE db SET RECOVERY FULL / SIMPLE |
Msg 40517, keyword or option not supported |
There is no exception in this family, and the two numbers are the parser's own distinction:
Msg 40510 rejects a whole statement, Msg 40517 rejects one option of a statement that is
otherwise supported, which is why ALTER DATABASE still renames a database in section 5. Every
row is a signal the agent is solving the wrong problem.
2. Find the restore point before you name it
--time must be at or after the source's earliestRestoreDate, so read it:
az sql db show --resource-group <rg> --server <server> --name <database> \
--query earliestRestoreDate --output tsv
A dropped database is not in az sql db show at all. It is a separate resource carrying the
deletion timestamp you need, and its name is a composite, so read databaseName:
az sql db list-deleted --resource-group <rg> --server <server> \
--query "[].{db:databaseName, deleted:deletionDate, earliest:earliestRestoreDate}" \
--output table
3. Three restores, three commands, and they do not have the same reach
Point-in-time, into a new name on the same server. Pass the source's own tier so the copy can stand in for it:
az sql db restore --resource-group <rg> --server <server> --name <database> \
--dest-name <database>-restored --time "2026-09-02T14:30:00" \
--edition GeneralPurpose --service-objective GP_Gen5_2 --backup-storage-redundancy Geo
A dropped database uses the same command with --deleted-time in place of, or alongside, --time,
matching deletionDate from the listing above exactly:
az sql db restore --resource-group <rg> --server <server> --name <dropped-database> \
--dest-name <dropped-database>-recovered --deleted-time "2026-09-02T09:12:41"
Geo-restore is a different command, and the backup is addressed by resource id:
az sql db geo-backup list --resource-group <rg> --server <server> --output table
az sql db geo-backup restore --geo-backup-id <geo-backup-resource-id> \
--dest-database <database> --dest-server <server-in-recovery-region> \
--resource-group <target-rg>
Long-term retention is a third command, with backup ids listed per region:
az sql db ltr-backup list --location <region> --server <server> --database <database> \
--output table
az sql db ltr-backup restore --backup-id <ltr-backup-resource-id> \
--dest-database <database> --dest-server <server> --dest-resource-group <rg>
Pick by reach, not by habit. The rows are Learn's, and the wrong choice wastes a restore rather than taking a slower route:
| Restore | Where it can land | What has to be true already |
|---|---|---|
| Point-in-time | the same server only, no cross-server, cross-subscription or cross-region | inside the retention window; the source is a primary, not a geo-secondary |
| Deleted database | the same server only | the server still exists; deleting a server deletes its databases and their backups together |
| Geo-restore | any server in any region, same subscription | backup storage redundancy was already Geo or GeoZone; it is not retroactive |
| Long-term retention | any server, and it outlives the source server | an LTR policy existed before the point you want |
So if the logical server itself was deleted, long-term retention is the only route left. Restoring across the Hyperscale boundary, in either direction, is not supported at all.
4. Retention is a setting, and the default is seven days
Both windows are set per database:
az sql db str-policy set --resource-group <rg> --server <server> --name <database> \
--retention-days 35 --diffbackup-hours 12
az sql db ltr-policy set --resource-group <rg> --server <server> --name <database> \
--weekly-retention P4W --monthly-retention P12M --yearly-retention P10Y --week-of-year 1
Short-term retention defaults to 7 days and takes 1 to 35, except DTU Basic which takes 1 to 7.
--diffbackup-hours accepts only 12 or 24. Each long-term retention value takes a minimum of 7
days and a maximum of 10 years, and --week-of-year is 1 to 52. Those bounds are in both
the CLI's own help and the Learn pages below.
5. The swap nobody automates
The restore leaves two databases: the broken one and the new one holding the data. Learn names the rename as the way to finish it. In T-SQL, measured on the engine above:
ALTER DATABASE [app-db] MODIFY NAME = [app-db-broken];
ALTER DATABASE [app-db-restored] MODIFY NAME = [app-db];
Or the same swap from the control plane, which still works when nothing can connect to run T-SQL:
az sql db rename --resource-group <rg> --server <server> --name <database> \
--new-name <database>-broken
az sql db rename --resource-group <rg> --server <server> --name <database>-restored \
--new-name <database>
Either way it is a cutover with a brief gap: pooled connections against the old name fail or reconnect depending on the driver. The alternative is to repoint the connection string and rename nothing, which has no gap but means finding every hardcoded copy of the database name, migration tooling included.
6. Getting recovered data out of the subscription
Point-in-time restore is same-server and geo-restore is same-subscription, so neither reaches another subscription or a workstation. Exporting the restored database is the only route out:
sqlpackage /Action:Export /TargetFile:recovered.bacpac \
/SourceServerName:<server>.database.windows.net /SourceDatabaseName:<database>-restored \
/SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" /p:VerifyExtraction=true
A bacpac is a logical copy of one moment, not a restore point. It cannot recover anything the retention window has already dropped; it only moves what a restore already produced.
Check it worked
A restore command returning is not a recovered database.
The database exists and is serving:
az sql db show --resource-group <rg> --server <server> --name <database>-restored \
--query "{name:name, status:status, sku:sku.name, tags:tags}" --output table
Expect status Online. Expect empty tags even on a healthy restore: Learn states a restore does
not carry the source's tags, so their absence is not a failure.
The rows are the rows you wanted, which the control plane cannot tell you. Run this against the restored name and again against the name the application uses, after the swap in section 5:
sqlcmd -S <server>.database.windows.net -d <database>-restored -U <user> \
-P "$SQLCMD_PASSWORD" -Q "SELECT DB_NAME() AS connected_to,
SUM(p.rows) AS row_count, COUNT(DISTINCT t.object_id) AS table_count
FROM sys.tables AS t JOIN sys.partitions AS p
ON p.object_id = t.object_id AND p.index_id IN (0, 1);"
Equal counts across the two names, or a stated reason they differ, is the check. A restore to a point before a bad backfill is expected to differ, and you should be able to say by how much. Until the second run returns the restored data, the recovery is not finished.
Do not
- Do not write
RESTORE DATABASE,BACKUP DATABASE, anyRESTORE ... ONLYvariant, orSET RECOVERYas the recovery step. All of them are refused, measured. - Do not say a restore updates the existing database. Learn says outright that you can't overwrite an existing database during restore.
- Do not stop when the restored database exists. The rename or connection string swap is part of the recovery.
- Do not promise a geo-restore carries firewall rules, the Microsoft Entra administrator, or server level logins to a different logical server. Those are server scoped, contained users are not, and none of it was verified here.
- Do not quote a fixed geo-restore RPO or RTO. Learn's figures for this service are "typically minutes or hours", dependent on storage replication and backup size; a fixed pair of hours comes from a different product's page.
- Do not size the target casually. Learn notes a restore may need S3 or above, which you can scale back down once it finishes.
References
- references/backup-restore-tsql-errors.md: open before restating any error number here as measured, or when an engine answers differently from the table in section 1.
- Recover using automated database backups: read it before a restore that crosses a server, a subscription or a region; it is where the scope limits in section 3 come from.
- Automated backups: read it when you need retention defaults or backup storage redundancy options.
- Long-term retention: read it before promising a restore point older than the short-term window.
- Business continuity overview: read it when the requirement is near zero downtime, which failover groups and active geo-replication solve and restore does not.
provision-azure-sql-db: creating a database and its firewall rule in the first place.provision-hyperscale: Hyperscale restore mechanics, which differ from the general case here.schema-migrations-safely: rolling back a bad schema change without touching data.sqlpackage-import-export: the full bacpac and dacpac surface behind the one command above.