Azure SQL Backups Done Right: Point-in-Time Restore, Long-Term Retention and Geo-Restore

Someone runs DELETE FROM Orders without a WHERE clause at 11:40 on a Tuesday, commits, and walks to lunch. Or a bad migration drops a column that three downstream services depend on. Or a region has a bad day and your primary database is simply gone. In every one of these moments the only question that matters is: can I get the data back, and to what point in time? On Azure SQL Database — the fully managed PaaS database where Microsoft, not you, runs the SQL Server engine — the answer is almost always yes, because the platform has been taking backups automatically the entire time. The catch is that “almost always” hides a dozen settings, three completely different restore mechanisms, and several ways to quietly lose the protection you assumed you had.

This article is the implementation guide. Azure SQL gives you three distinct tools, and conflating them is how teams get hurt. Point-in-time restore (PITR) rewinds a database to any second within a short retention window (1–35 days) — your answer to “someone broke the data an hour ago.” Long-term retention (LTR) keeps weekly, monthly and yearly full-backup copies for up to ten years — your answer to “the auditor wants the year-end 2024 dataset.” Geo-restore rebuilds the database in a different region from a geo-replicated backup — your answer to “the primary region is down.” They substitute for each other never: PITR cannot give you last year, LTR cannot give you 11:39 this morning, and neither protects against a region loss the way geo-restore does. You will configure all three end to end — portal, az CLI and Bicep — and you will test a restore, because a backup you have never restored is a hope, not a plan.

By the end you will know what is backed up and when, how to set a retention window that matches your real recovery needs, how to restore to a precise timestamp and validate it, how to stand up LTR policies as code and pull a year-old backup, and how to fail a database into another region with geo-restore. You will also know the sharp edges: a restore always creates a new database (it never overwrites), LTR backups survive deletion of the source server, backup storage redundancy is chosen at creation and silently caps where geo-restore can land you, and the restore clock (RTO) is driven by database size, not by how recently the disaster happened.

What problem this solves

Data loss on a managed database is rarely a hardware failure — the platform handles those invisibly with redundant storage. The losses that actually happen are logical: a human or a script mutates or deletes data that was correct a moment ago, and the change commits successfully. Storage redundancy faithfully replicates the corruption. The only escape is a copy of the data from before the mistake and the ability to bring it back fast and to the right point in time. That is what backups are for, and on Azure SQL the machinery exists by default — but defaults are tuned for cost, not for your worst day.

What breaks without this knowledge: a team discovers a bad delete and either tries to “undo” it with more writes (compounding the damage) or opens a support ticket assuming Microsoft can roll back their production database in place — Microsoft cannot and will not; you drive the restore. Worse, a team that never set up LTR finds the PITR window has already rolled past the corruption (it happened nine days ago, the window is seven) and the data is genuinely unrecoverable. Or a region outage hits and the team learns — at the worst time — that their backup storage was LRS, so there is no geo-replicated copy to restore from.

Who hits this: everyone who runs a production database, but hardest on teams that treated “Azure backs it up automatically” as the end of the conversation rather than the start. The fix is cheap and entirely within your control: knowing which of the three tools answers which question, and having tested the restore before you needed it.

To frame the field before the deep dive — every recovery tool Azure SQL gives you, the question it answers, and its blunt limit:

Learning objectives

By the end of this article you can:

• Explain what Azure SQL backs up automatically (full, differential, transaction-log backups) and the cadence of each, and why that cadence makes second-level PITR possible.
• Configure the PITR retention window (1–35 days) on a database via the portal, az CLI and Bicep, and pick a window that matches your real recovery need without overpaying for storage.
• Perform a point-in-time restore to an exact timestamp, validate that the restored database holds the correct data, and cut over to it safely (since restore never overwrites in place).
• Define a long-term retention (LTR) policy with weekly/monthly/yearly retention up to ten years, restore a specific LTR backup, and reason about the cost of long retention.
• Run a geo-restore into a different region, and explain how backup storage redundancy (LRS / ZRS / GRS / GZRS) determines whether geo-restore is even possible.
• Distinguish RPO (how much data you can lose) from RTO (how long recovery takes) for each mechanism, and give realistic figures for each.
• Restore a deleted database, and avoid the top mistakes: assuming in-place rollback, letting the PITR window lapse past the incident, and mismatching backup redundancy to your DR needs.

Prerequisites & where this fits

You should already be comfortable creating an Azure SQL Database and connecting to it — if you are brand new, start with Create and Connect to Your First Azure SQL Database and come back. You should know how to run az commands in Cloud Shell, read JSON output, and that Azure SQL has two layers: a logical server (Microsoft.Sql/servers, the *.database.windows.net endpoint and security boundary) and the databases that live on it. Familiarity with the purchasing models helps, because backup storage is billed differently depending on whether you are on DTU or vCore — see Azure SQL DTU vs vCore Purchasing Models Explained.

This sits in the Data protection & business continuity track. Backups are the foundation; the layer above them — keeping a hot, continuously-replicated copy for near-zero-downtime failover — is active geo-replication and failover groups, which solve a different problem (high availability and very low RPO) and are out of scope here. The conceptual framing for the whole field — what RPO and RTO mean and how to set targets — is in Business Continuity & Disaster Recovery: RTO & RPO Fundamentals, and it is worth reading alongside this. If you run SQL Server inside a VM rather than as PaaS, backups work completely differently — see Back Up SQL Server in an Azure VM: Policies & Restore instead; this article is strictly about the managed Azure SQL Database service.

A quick map of which layer answers which incident, so you reach for the right tool fast:

Core concepts

Five mental models make every later step obvious.

Backups are automatic, continuous, and free to take. From the moment a database exists, Azure SQL takes backups for you — you do not schedule them, run them, or manage the storage account they land in. It takes full backups (a complete copy, ~weekly), differential backups (everything changed since the last full, ~every 12–24 h), and transaction-log backups (the stream of committed changes, ~every 5–10 min). You pay only for the storage those backups consume beyond a free allowance, not for the act of backing up. This trio is the entire reason point-in-time restore works: replay the right full + differential, then roll the transaction log forward to the exact second you ask for.

A restore always creates a new database — it never overwrites. This is the single most important fact and the one people forget under pressure. Every restore mechanism (PITR, LTR, geo-restore, deleted-DB restore) produces a brand-new database you name, sitting alongside the untouched original. It is a safety feature: a restore can never make things worse, and you can compare old and new before cutting over. The corollary is that restoring is only half the job — once the new database is healthy with the right data, you still have to point your application at it (or rename it into place), and that cutover is a deliberate step you own.

PITR, LTR and geo-restore are three tools for three different questions — not tiers of the same thing. PITR uses the automated backup chain to reach any second within a short, rolling window (1–35 days). LTR copies certain full backups out to long-term storage on a schedule you define (weekly/monthly/yearly) and keeps them up to ten years — discrete points, not second-level. Geo-restore restores from a geo-replicated copy of the backups in the paired region, so you can recover even if the primary region is unreachable. Asking PITR for last year, asking LTR for “11:39 this morning,” or expecting geo-restore from non-geo-redundant storage are the three classic category errors.

RPO and RTO differ per mechanism, and RTO scales with size. RPO (Recovery Point Objective) is how much data you might lose; RTO (Recovery Time Objective) is how long recovery takes. For PITR within a region the RPO is effectively the log-backup interval (minutes), but for geo-restore it can be up to about an hour, because the geo-replicated backups lag the primary. RTO for any restore is driven mostly by the size of the database — the platform must materialise a new database from the backups — so a 2 TB restore takes far longer than a 5 GB one, and that determines how long your outage lasts.

Backup storage redundancy is chosen at creation and dictates geo-restore. Backups are stored with a redundancy level — LRS (3 copies, one datacentre), ZRS (3 across zones in the region), GRS (replicated to the paired region), or GZRS (zone-redundant and geo-replicated). GRS is the default and is what makes geo-restore possible. Set it to LRS or ZRS (often a cost script does) and the backups never leave the primary region, so geo-restore is simply not available — which you discover during a regional outage, the worst time. Choose it deliberately and document it.

The vocabulary in one table

Before the deep sections, pin down every moving part. The glossary at the end repeats these for lookup; this table is the mental model side by side:

How automated backups actually work

You never touch a backup job on Azure SQL — the platform runs them — but the cadence tells you exactly what you can recover and how fast. Three backup types form a chain that PITR replays to a timestamp:

When you request a point-in-time restore to, say, 2026-06-24T11:39:00Z, the platform takes the most recent full backup before that time, applies the latest differential before it, then replays transaction-log backups forward until it reaches 11:39:00 — and stops. The result is a new database byte-for-byte identical to the original at 11:39:00. Because log backups happen every few minutes, your worst-case data loss for an in-region PITR is essentially the log-backup interval, and the restore is only as fresh as the last log backup — a request for “right now” lands seconds-to-minutes ago. Two planning facts follow: backups run automatically from creation (you cannot forget to back up, but you can mis-set retention), and a brand-new database has a restorable base within minutes, not days.

Point-in-time restore (PITR): the everyday tool

PITR is the tool you reach for ninety percent of the time, because the disasters that actually happen — a bad delete, a broken migration, a buggy batch job — are recent and logical. PITR rewinds to any second inside a rolling retention window.

Setting the retention window (1–35 days)

The PITR retention window defaults to 7 days and can be set anywhere from 1 to 35 days. Longer windows give you more room to discover and recover from a problem, at the cost of more backup storage. Seven days is fine for many workloads; regulated or slow-to-notice workloads often want the full 35.

Set it with az:

# Set PITR retention to 14 days on a single database
az sql db str-policy set \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --retention-days 14

Read it back to confirm:

az sql db str-policy show \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --query "{retentionDays:retentionDays}" -o table

In Bicep, the short-term retention policy is a child resource of the database:

resource ordersDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  parent: sqlServer
  name: 'db-orders'
  location: location
  sku: { name: 'GP_S_Gen5', tier: 'GeneralPurpose', family: 'Gen5', capacity: 2 }
}

resource shortTermRetention 'Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies@2023-08-01-preview' = {
  parent: ordersDb
  name: 'default'
  properties: {
    retentionDays: 14   // 1–35
  }
}

How to choose the window — the trade-off is real backup-storage cost versus how long you have to notice and recover:

A key reading: the window is the maximum age of a recoverable point. If corruption happened 9 days ago and your window is 7, the clean data is already gone from PITR — precisely the gap LTR exists to cover.

Performing a point-in-time restore

A PITR creates a new database restored to your chosen timestamp. The original keeps running untouched.

# Restore db-orders to a precise UTC timestamp into a NEW database
az sql db restore \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --dest-name db-orders-restored-1139 \
  --time "2026-06-24T11:39:00Z"

Notes that save you grief:

• The --time value is UTC and must fall inside the current retention window — earlier than the window’s start fails, later than the latest restorable point fails.
• The destination (--dest-name) must not already exist; restore creates it fresh on the same server (or a different server in the same subscription/region). It starts at the source’s service tier — resize after if needed.

Find the earliest restorable point so you don’t guess at an out-of-window timestamp:

az sql db show \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --query "{earliest:earliestRestoreDate}" -o table

The PITR parameters you actually set, and the gotcha on each:

Validating the restore before you cut over

The restore is not done when the database appears — it is done when you have confirmed the data is right and switched the application over. Because PITR never overwrites, you can check first.

# 1. Confirm the restored DB is Online
az sql db show -g rg-sqlbackup-prod -s sql-shop-prod -n db-orders-restored-1139 \
  --query "{name:name, status:status, sizeBytes:maxSizeBytes}" -o table

# 2. Connect and spot-check the data (e.g. the rows that were deleted are back)
#    sqlcmd -S sql-shop-prod.database.windows.net -d db-orders-restored-1139 -G \
#      -Q "SELECT COUNT(*) AS RowsBack FROM dbo.Orders WHERE OrderDate >= '2026-06-24';"

Cutover options once validated:

The selective copy-back case is subtle and common: if good transactions landed after the bad delete, a full cutover loses them — so you treat the restored database as a source and merge the recovered rows back into the live database rather than swapping wholesale.

Long-term retention (LTR): keeping years, not days

PITR maxes out at 35 days. When compliance, finance or legal needs a snapshot from months or years ago, that is long-term retention. LTR copies specific full backups to separate long-term storage on a schedule you define and keeps them up to ten years.

How LTR retention is expressed

An LTR policy has up to four independent dials, each saying “keep one backup at this cadence for this long”:

These stack: a policy can keep weekly for 12 weeks, monthly for 12 months and yearly for 7 years at once, and each backup is retained for the longest applicable rule. Retention values use ISO-8601 duration syntax in the API (P12W, P12M, P7Y).

Configuring an LTR policy

With az:

# Keep weekly backups 12 weeks, monthly 12 months, yearly 7 years (year backup = week 1)
az sql db ltr-policy set \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --weekly-retention "P12W" \
  --monthly-retention "P12M" \
  --yearly-retention "P7Y" \
  --week-of-year 1

Confirm the policy:

az sql db ltr-policy show \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders -o json

In Bicep:

resource ltrPolicy 'Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies@2023-08-01-preview' = {
  parent: ordersDb
  name: 'default'
  properties: {
    weeklyRetention: 'P12W'    // 12 weeks
    monthlyRetention: 'P12M'   // 12 months
    yearlyRetention: 'P7Y'     // 7 years
    weekOfYear: 1              // which week the yearly backup comes from
  }
}

LTR backups do not appear instantly — the policy takes effect going forward, and the first qualifying weekly backup starts populating the long-term store. You cannot retroactively LTR-archive a backup taken before the policy existed.

Listing and restoring an LTR backup

LTR backups are addressed individually. List what exists, then restore the one you want:

# List available LTR backups for the database
az sql db ltr-backup list \
  --location centralindia \
  --server sql-shop-prod \
  --database db-orders \
  --query "[].{name:name, time:backupTime, expiry:backupExpirationTime}" -o table

# Restore a specific LTR backup (by its resource ID) into a NEW database
az sql db ltr-backup restore \
  --dest-resource-group rg-sqlbackup-prod \
  --dest-server sql-shop-prod \
  --dest-database db-orders-fy2025-audit \
  --backup-id "/subscriptions/<sub>/resourceGroups/.../longTermRetentionBackups/<backup-name>"

Two distinctions from PITR routinely surprise people. First, granularity: PITR reaches any second in its window, whereas LTR only gives you the specific weekly/monthly/yearly points the policy kept. Second — and far bigger — LTR backups survive deletion: delete the database (or even the logical server) and the LTR backups remain restorable, because they live in subscription-scoped long-term storage, not on the server. PITR backups, by contrast, are gone once the database is gone (beyond the deleted-database restore window). This is exactly why compliance retention belongs in LTR, not in a long PITR window, and why LTR is billed separately from PITR’s backup storage.

Geo-restore: recovering from a regional outage

PITR and LTR both recover within a region. If the whole primary region is unavailable, you need a copy of the backups elsewhere — and that is geo-restore, which restores from the geo-replicated backups held in the Azure paired region.

Why it depends on backup storage redundancy

Geo-restore only works if the database’s backup storage redundancy replicates to the paired region. The four options, and crucially which ones permit geo-restore:

The trap: GRS is the default, but a team optimising cost might set backup storage to LRS or ZRS without realising they have just disabled geo-restore. There is no warning in normal operation — you discover the gap only when you try to geo-restore during a real regional incident. For anything that must survive a region loss, keep GRS (or GZRS for zone resilience too). For the deeper redundancy model this mirrors, see Azure Storage Redundancy: LRS, ZRS, GRS & RA-GRS Explained.

Set backup storage redundancy at database creation (changing it later is limited):

# Create a database with geo-zone-redundant backup storage (geo-restore enabled, zone-resilient)
az sql db create \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --service-objective GP_S_Gen5_2 \
  --backup-storage-redundancy GeoZone

resource ordersDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  parent: sqlServer
  name: 'db-orders'
  location: location
  sku: { name: 'GP_S_Gen5', tier: 'GeneralPurpose', family: 'Gen5', capacity: 2 }
  properties: {
    requestedBackupStorageRedundancy: 'GeoZone'   // Geo | GeoZone | Zone | Local
  }
}

Running a geo-restore

Geo-restore creates a new database in a target region of your choice from the latest geo-replicated backup. You typically restore into a logical server that already exists in the secondary region (create one first if you don’t have it).

# Get the recoverable database's ID (the geo-replicated backup), then restore it into the secondary region's server
RECOVERABLE_ID=$(az sql recoverable-database show \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --database db-orders \
  --query id -o tsv)

az sql db restore \
  --resource-group rg-sqlbackup-dr \
  --server sql-shop-dr \
  --name db-orders-georestored \
  --geo-restore true \
  --recoverable-database-id "$RECOVERABLE_ID" \
  --backup-storage-redundancy Geo

What to expect, and the limits:

Geo-restore is your disaster recovery, not your high-availability tool: with its up-to-an-hour RPO and size-driven RTO, it answers “the region is gone and I need the database back somewhere.” If you need near-zero RPO and fast failover instead, that is active geo-replication / failover groups — a hotter mechanism beyond this article’s scope.

Restoring a deleted database

Dropping the wrong database happens. As long as you are still inside the backup retention window of that database, Azure SQL can bring it back — its backups are kept until the retention period from its deletion time lapses.

# List databases that were deleted but are still restorable on this server
az sql db list-deleted \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --query "[].{name:name, deletionDate:deletionDate}" -o table

# Restore a deleted database into a new database (you need its original name + deletion time)
az sql db restore \
  --resource-group rg-sqlbackup-prod \
  --server sql-shop-prod \
  --name db-orders \
  --dest-name db-orders-recovered \
  --deleted-time "2026-06-23T18:05:00Z"

The catch that loses data: the recoverability of a deleted database is bounded by its retention window. Delete a database that had a 7-day PITR window, wait 8 days, and it is gone for good — unless it had an LTR policy, in which case the LTR backups survive the deletion entirely. This is one more argument for LTR on anything you cannot afford to lose: it is the only protection that outlives the database itself.

Architecture at a glance

Hold the system as a layered mental model and the right tool for any incident becomes obvious. Picture your live database on its logical server in the primary region. Underneath it, invisibly, the platform continuously writes three streams of backups into backup storage: a weekly full, twice-daily differentials, and a transaction-log backup every few minutes. That backup storage has a redundancy level — by default GRS, so it is also being asynchronously copied to the paired region. Those backups are the raw material every restore draws from.

Now layer the three recovery paths on top of that picture. A point-in-time restore reaches down into the in-region backup chain and replays full + differential + log forward to an exact second within the 1–35-day window, producing a brand-new database beside the original. A long-term retention policy taps the weekly full backups as they are taken and copies them sideways into separate long-term storage, where they live for up to ten years independent of the source database — so even if the database is deleted, those archived points remain restorable. A geo-restore reaches across to the geo-replicated copy in the paired region and builds a new database there, which is the only one of the three that survives the primary region going dark.

The mental shortcut: every restore is a read from one of these backup stores into a new database, and the question “how recent, how far back, and is my region up?” tells you instantly which store to read from. Choose your backup storage redundancy at creation so the across path exists before you need it, and size your database with restore-time (RTO) in mind — the bigger it is, the longer any of these reads takes to materialise.

Real-world scenario

Saffron Ledger, a mid-sized fintech, runs its core ledger on a single Azure SQL Database — a General Purpose, 4 vCore database, about 180 GB, in Central India. The data team is three engineers; the database backs a loan-servicing platform, so it is both performance-sensitive and compliance-bound (the regulator requires recoverable point-in-time data for seven years). When they first deployed, they accepted defaults: a 7-day PITR window, GRS backup storage, and no LTR policy — because nobody had asked the compliance question yet.

The first incident was mundane and instructive. A nightly reconciliation job’s faulty WHERE clause marked 42,000 active loans as closed at 02:10 on a Saturday. Nobody noticed until Monday 09:30 — the loans simply stopped appearing in customer portals over the weekend. The on-call engineer’s instinct was a correcting UPDATE, but the prior state couldn’t be reconstructed from live data (the job had overwritten the status and timestamp). The right move was PITR: they restored to 02:00 Saturday — ten minutes before the bad job ran — into db-ledger-restored-0200, confirmed the 42,000 loans showed Active, and then, because good customer transactions had landed Saturday through Monday that they could not lose, did not cut over wholesale. Instead they treated the restored DB as a source and MERGEd only the corrupted loan-status rows back into the live database. Data lost: zero. Time to recover: about 90 minutes, most of it materialising the 180 GB restore and validating, not deciding.

The second incident never happened — but the audit did. Six months in, the regulator asked for the loan book as it stood at the previous fiscal year-end (31 March) — four months past, well beyond the 7-day PITR window. Had they not changed anything, the data would have been unrecoverable and the audit failed. But the first incident had triggered a review, and they had since set an LTR policy: weekly for 12 weeks, monthly for 12 months, and yearly for 7 years with week-of-year pinned to the last week of March. The year-end backup was sitting in long-term storage exactly as required. They ran az sql db ltr-backup list, found the 31-March backup, restored it into db-ledger-fy-audit, handed the auditor a read-only copy, and deleted it after. The LTR storage for those yearly snapshots cost roughly ₹1,200–1,800/month — trivial against a failed regulatory audit.

The third lesson was about redundancy, from a near-miss at a sister team that had set their backup storage to LRS during a cost-cutting sprint. When Central India had a brief zonal disruption they reached for geo-restore — and found it did not exist, because LRS keeps no cross-region copy. Their database recovered on its own once the zone came back, so they got lucky, but the postmortem was blunt: backup redundancy is a recovery decision, not a cost line. Saffron Ledger’s GRS default had quietly given them that option all along. Three lessons went on the runbook: PITR for recent mistakes (and merge back, don’t always cut over); LTR for anything compliance asks for, because it outlives the database; and never downgrade backup redundancy below GRS for production.

Advantages and disadvantages

Automated, multi-tier backups on a managed database are a genuine strength, but the abstraction has sharp edges worth weighing honestly:

The model is right for the overwhelming majority of OLTP workloads: you want to ship application features, not run a backup infrastructure, and the platform’s automated chain plus PITR/LTR/geo-restore covers the real failure modes. It bites teams that (a) never configured LTR and needed long retention, (b) downgraded redundancy to save money and lost geo-restore, or © assumed a restore is an in-place undo and were surprised by the new-database-and-cutover reality. Every one of those is avoidable by knowing the three tools and testing a restore — which is the entire point of this article.

Hands-on lab

This is the centerpiece: you will create a database, set its PITR window and an LTR policy, deliberately corrupt some data, restore it to a point in time, validate the recovery, and then exercise the LTR and geo-restore paths — in both the portal and the az CLI, with a Bicep version — and tear it all down. Everything here is small (a Basic/serverless database) and costs only a few rupees for the hour. Run the CLI parts in Cloud Shell (Bash).

Prerequisites for the lab

Part A — Create the database (CLI)

Step 1 — Variables and resource group.

RG=rg-sqlbackup-lab
LOC=centralindia
SQLSERVER=sql-lab-$RANDOM            # logical server name must be globally unique
ADMINUSER=sqladmin
ADMINPASS='P@ssw0rd-Lab-2026!'       # use a strong, unique password
DB=db-lab
az group create -n $RG -l $LOC -o table

Expected: a JSON/table row showing "provisioningState": "Succeeded".

Step 2 — Create the logical server.

az sql server create \
  --name $SQLSERVER --resource-group $RG --location $LOC \
  --admin-user $ADMINUSER --admin-password "$ADMINPASS" -o table

Expected: a server row with state = Ready.

Step 3 — Create the database with GRS backup storage (geo-restore enabled). We use a small serverless General Purpose SKU to keep cost minimal.

az sql db create \
  --resource-group $RG --server $SQLSERVER --name $DB \
  --edition GeneralPurpose --compute-model Serverless \
  --family Gen5 --capacity 1 \
  --backup-storage-redundancy Geo \
  -o table

Expected: a database row, status = Online. (Serverless may show as Online and auto-pause later — fine for the lab.)

Step 4 — Allow your client through the firewall so you can run queries.

# Allow Azure services + your current public IP (replace with your IP if running locally)
MYIP=$(curl -s https://api.ipify.org)
az sql server firewall-rule create -g $RG -s $SQLSERVER \
  -n allow-my-ip --start-ip-address $MYIP --end-ip-address $MYIP -o table

If firewall or login concepts trip you up here, the dedicated walkthrough is Create and Connect to Your First Azure SQL Database, and login-specific failures are covered in Troubleshooting Azure SQL: Login Failed, Firewall & AAD Auth Basics.

Part B — Configure retention (CLI + Bicep)

Step 5 — Set the PITR window to 14 days.

az sql db str-policy set -g $RG -s $SQLSERVER -n $DB --retention-days 14
az sql db str-policy show -g $RG -s $SQLSERVER -n $DB --query "{retentionDays:retentionDays}" -o table

Expected: retentionDays : 14.

Step 6 — Set an LTR policy (weekly 12 weeks, monthly 12 months, yearly 5 years).

az sql db ltr-policy set -g $RG -s $SQLSERVER -n $DB \
  --weekly-retention "P12W" --monthly-retention "P12M" --yearly-retention "P5Y" --week-of-year 1
az sql db ltr-policy show -g $RG -s $SQLSERVER -n $DB -o json

Expected: JSON echoing weeklyRetention: P12W, monthlyRetention: P12M, yearlyRetention: P5Y, weekOfYear: 1. (Note: no LTR backup files exist yet — the policy populates them as weekly backups are taken going forward. This is the “set it up before you need it” rule in action.)

Bicep equivalent of Parts A–B. Save as sql-backup-lab.bicep and deploy with az deployment group create -g $RG -f sql-backup-lab.bicep -p adminPassword="$ADMINPASS":

param location string = resourceGroup().location
param serverName string = 'sql-lab-${uniqueString(resourceGroup().id)}'
param adminLogin string = 'sqladmin'
@secure()
param adminPassword string

resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {
  name: serverName
  location: location
  properties: {
    administratorLogin: adminLogin
    administratorLoginPassword: adminPassword
    minimalTlsVersion: '1.2'
  }
}

resource labDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  parent: sqlServer
  name: 'db-lab'
  location: location
  sku: { name: 'GP_S_Gen5', tier: 'GeneralPurpose', family: 'Gen5', capacity: 1 }
  properties: {
    requestedBackupStorageRedundancy: 'Geo'   // geo-restore enabled
  }
}

resource shortTerm 'Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies@2023-08-01-preview' = {
  parent: labDb
  name: 'default'
  properties: { retentionDays: 14 }
}

resource longTerm 'Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies@2023-08-01-preview' = {
  parent: labDb
  name: 'default'
  properties: {
    weeklyRetention: 'P12W'
    monthlyRetention: 'P12M'
    yearlyRetention: 'P5Y'
    weekOfYear: 1
  }
}

resource allowAzure 'Microsoft.Sql/servers/firewallRules@2023-08-01-preview' = {
  parent: sqlServer
  name: 'AllowAllWindowsAzureIps'
  properties: { startIpAddress: '0.0.0.0', endIpAddress: '0.0.0.0' }
}

Part C — Create data, then break it

Step 7 — Seed a table with rows. Use the portal Query editor (SQL databases → your DB → Query editor, log in with the admin), Azure Data Studio, or sqlcmd:

CREATE TABLE dbo.Orders (
  OrderId   INT IDENTITY PRIMARY KEY,
  Customer  NVARCHAR(100),
  Amount    DECIMAL(10,2),
  Status    NVARCHAR(20) DEFAULT 'Active',
  CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

INSERT INTO dbo.Orders (Customer, Amount) VALUES
 ('Asha',  1200.00), ('Rahul', 4500.00), ('Meera', 780.00),
 ('Vikram', 9900.00), ('Neha',  330.00);

SELECT COUNT(*) AS TotalRows FROM dbo.Orders;   -- expect 5

Step 8 — Note a clean timestamp, then wait a few minutes. Record the current UTC time as your “known-good” point, then pause long enough for a transaction-log backup to capture this state (a few minutes):

SELECT SYSUTCDATETIME() AS KnownGoodUtc;   -- copy this value

Important: PITR can only restore to a point the backup chain has actually captured. A database created seconds ago has no restorable history yet — that is why we let a few minutes pass before corrupting the data.

Step 9 — Break the data (the “bad delete”).

DELETE FROM dbo.Orders WHERE Amount > 1000;   -- oops: removes 3 rows
SELECT COUNT(*) AS RowsAfterBadDelete FROM dbo.Orders;   -- now 2

You have now reproduced the everyday disaster: committed data loss the live database can no longer show you.

Part D — Point-in-time restore (CLI + portal)

Step 10 — Restore to the known-good timestamp (CLI). Use the KnownGoodUtc value from Step 8, in YYYY-MM-DDTHH:MM:SSZ form:

az sql db restore -g $RG -s $SQLSERVER -n $DB \
  --dest-name ${DB}-restored \
  --time "2026-06-24T07:25:00Z"     # <-- your KnownGoodUtc value

Expected: the command returns a database object for db-lab-restored once the restore completes (this can take a few minutes — restore time scales with size; a tiny lab DB is quick). If you get “specified time is before the earliest restore point,” your timestamp is older than the backup chain — pick a slightly later time, or check earliestRestoreDate:

az sql db show -g $RG -s $SQLSERVER -n $DB --query earliestRestoreDate -o tsv

Step 10 (portal alternative). In the portal: SQL databases → db-lab → Overview → Restore. Set Source = Point-in-time, pick the date/time matching your known-good moment (the picker enforces the valid window), give the target a name (db-lab-restored), and select Review + create. The portal does exactly what the CLI does — creating a new database — and shows the restore progress under the target database’s notifications.

Step 11 — Validate the recovery. Query the restored database and confirm all five rows are back:

-- Against db-lab-restored
SELECT COUNT(*) AS RecoveredRows FROM dbo.Orders;        -- expect 5
SELECT * FROM dbo.Orders WHERE Amount > 1000 ORDER BY OrderId;  -- the 3 "deleted" rows

Expected: RecoveredRows = 5, and the three high-value rows you deleted are present. The original db-lab still shows 2 — proving the restore created a new database and never touched the original.

Step 12 — Decide the cutover. In real life you would now either repoint the app to db-lab-restored, rename it into place, or (if good writes landed after the bad delete) MERGE just the lost rows back into db-lab. For the lab, confirming the data is recovered is enough — the cutover decision is the architectural lesson, not a command to memorise.

Part E — Explore LTR and geo-restore paths

Step 13 — List LTR backups (likely empty in a fresh lab).

az sql db ltr-backup list --location $LOC --server $SQLSERVER --database $DB \
  --query "[].{time:backupTime, expiry:backupExpirationTime}" -o table

Expected: empty or a single entry. In a brand-new lab there is usually nothing yet — which is the lesson: LTR protects you only from the moment the policy starts capturing weekly backups, so it must be configured well ahead of when you will need it. (The restore command, for when a backup exists, is az sql db ltr-backup restore with the backup’s --backup-id, as shown earlier.)

Step 14 — Inspect geo-restore feasibility. Confirm the database’s backup storage is geo-capable (we set Geo at creation), which is the prerequisite for geo-restore:

az sql db show -g $RG -s $SQLSERVER -n $DB \
  --query "{name:name, redundancy:requestedBackupStorageRedundancy}" -o table

Expected: redundancy : Geo. A full geo-restore would create a server in the paired region and restore into it (az sql db restore ... --geo-restore true), but spinning up a second region is beyond a quick lab — the key takeaway is that this Geo value is what makes geo-restore possible at all. Had it shown Local, geo-restore would be unavailable.

Validation checklist

Teardown

az group delete -n $RG --yes --no-wait

Cost note. A serverless GP database used for an hour, plus a small restored copy, is a few rupees of compute and trivial backup storage; deleting the resource group stops all of it. Serverless auto-pauses when idle, so even a forgotten lab DB costs little — but delete the RG to be sure.

Common mistakes & troubleshooting

The failure modes that actually cost teams data or time. Scan the table, then read the detail for whichever row bit you.

The expanded reasoning for the two that bite hardest:

The corruption is older than the recoverable window (row 2). Root cause: The PITR window (default 7 days) had already rolled past the moment of corruption, and no LTR policy existed to keep an older point. Confirm: az sql db show --query earliestRestoreDate shows the oldest reachable point is after the corruption; az sql db ltr-policy show shows no retention configured. Fix: There may be no recovery for data already past the window — the hard lesson. Going forward, lengthen the PITR window (up to 35 days) and configure LTR so slow-to-detect issues have a fallback. This is why both exist.

Cut over to the restored DB and lost recent good writes (row 10). Root cause: A wholesale cutover to a database restored to before the incident also discards every legitimate transaction that committed after it. Confirm: The restored DB’s latest CreatedAt/ModifiedAt is earlier than the original’s; row counts of recent activity differ. Fix: When good writes followed the bad event, do not swap wholesale. Restore to just before the incident, then MERGE/copy only the corrupted rows back into the live database, preserving the good writes — the most common subtle data-loss trap in restores.

Best practices

• Set the PITR window to match how long it realistically takes you to notice a problem, not the minimum. If issues sometimes surface over a weekend or a fortnight, use 14 days; for slow-detection or regulated workloads, use the full 35. The storage cost is modest next to unrecoverable data.
• Configure LTR the day you create anything compliance- or audit-bound — not when someone asks. LTR cannot retroactively keep a backup taken before the policy existed.
• Keep backup storage at GRS (default) or GZRS for any production database that must survive a regional outage. Never downgrade to LRS/ZRS to save money on something that needs geo-restore — the gap is invisible until the disaster.
• Test a restore before you need one. A backup you have never restored is unverified. Periodically PITR into a throwaway database and confirm the data, the size, and the time it takes — that last number is your RTO.
• Remember every restore creates a new database; plan the cutover. Decide in advance whether you repoint the connection string, rename into place, or merge rows back — and document it in the runbook.
• When good writes follow an incident, merge — don’t cut over wholesale. Restoring to before the incident and swapping loses the legitimate transactions in between. Treat the restored DB as a source for the lost rows.
• Right-size redundancy to the data’s importance, deliberately. GZRS for the crown-jewel database; LRS only for genuinely disposable dev/test where you accept no geo-restore.
• Account for RTO = f(database size). A multi-TB database is a long restore; if you need fast recovery, pair backups with failover groups / active geo-replication, which backups alone do not provide.
• Use LTR — not a maxed-out PITR window — for long retention. 35 days of PITR is not “almost a year”; anything beyond a month belongs in LTR, which also survives database deletion. Review LTR and window storage cost as they grow, and trim what you don’t need.
• Manage retention and redundancy as code (Bicep). A reviewed requestedBackupStorageRedundancy and explicit retention policies prevent a console click or a cost script from silently weakening your recovery posture.

Security notes

• Restored databases inherit data, not all access. A restore copies data and schema, but server-level logins, firewall rules and identity config live on the server, not in the backup. Confirm the restored database’s access controls before exposing it, and lock down any throwaway restore that holds production data.
• Treat restored copies as production data. A PITR/LTR copy holds the same sensitive rows as the original — don’t leave validation copies on open firewall rules; delete them when done (as the lab does).
• Encryption follows the data. Azure SQL backups are encrypted at rest and Transparent Data Encryption (TDE) state carries through restores. With customer-managed keys (CMK/BYOK), ensure the key is available in the target region/server before a geo-restore — a missing key blocks the restore from coming online.
• Least-privilege the restore operation. Restoring can expose historical data; grant restore rights via RBAC (SQL DB Contributor or a custom role) only to those who need them, and audit restores in the activity log.
• Geo-restore crosses a region boundary — mind data residency. Restoring into a different region may move data across a geography; confirm that is permitted before geo-restoring to an arbitrary region (the paired region typically stays within the same geography).
• Protect the credentials you validate with. The admin login you use to spot-check a restore is a high-value secret; store it in Key Vault, not in scripts — see Azure Key Vault: Secrets, Keys & Certificates.

Cost & sizing

What drives the backup bill and how to keep it sane:

• PITR backup storage is partly free, then billed. Azure SQL includes backup storage roughly equal to your database’s size at no extra charge; you pay for storage beyond that allowance. It grows with the length of the PITR window and how much the data churns — a 14-day window on a busy database costs more than 7 days on a quiet one.
• LTR storage is separate and additive. Each retained weekly/monthly/yearly full backup occupies storage you pay for as long as the policy keeps it — multi-year retention on a large database is the line item that surprises people.
• Backup storage redundancy changes the per-GB rate. GRS/GZRS cost more per GB than LRS/ZRS because the data is replicated further — the real trade-off behind the redundancy choice: a little more per GB for the ability to geo-restore.
• Restores cost like a new database. A restored DB consumes compute and storage from the moment it’s online, so a validation copy left running bills like any other database. Delete throwaway restores promptly (and prefer serverless for validation, which auto-pauses).
• RTO and size: sizing for fast recovery isn’t a backup setting — it’s an architecture choice. If your RTO target is tight and the database is large, backups alone won’t meet it; budget for failover groups instead.

A rough INR/USD picture for a ~100 GB production database:

The headline: backups are one of the cheapest insurance policies in Azure. The expensive mistake is never the storage bill — it is unrecoverable data from a too-short window, missing LTR, or an LRS downgrade. Right-size the window, configure LTR for anything you must keep, keep GRS for anything that must survive a region, and the monthly cost stays small.

Interview & exam questions

1. What three recovery mechanisms does Azure SQL Database provide, and which question does each answer? PITR rewinds to any second within a 1–35-day window — for recent logical mistakes. LTR keeps weekly/monthly/yearly full backups up to 10 years — for compliance/audit snapshots. Geo-restore restores from a geo-replicated copy in the paired region — for recovery after a regional outage. They do not substitute for one another.

2. A developer deleted critical rows two hours ago. Which mechanism, and what is the one thing to remember about the result? PITR, restoring to just before the deletion. Remember: the restore creates a new database and never overwrites the original — so after validating the recovered data you must cut over (repoint/rename), or merge only the lost rows back if good writes followed the delete.

3. What is the maximum PITR retention, and what do you use beyond it? PITR maxes at 35 days. Beyond that you must use long-term retention (LTR), which keeps full backups up to 10 years. A maxed PITR window is not a substitute for LTR, and LTR backups additionally survive deletion of the source database.

4. During a regional outage you try to geo-restore and it’s unavailable. Most likely cause? The database’s backup storage redundancy is LRS or ZRS, so backups were never replicated to the paired region. Geo-restore requires GRS (default) or GZRS. Confirm with az sql db show --query requestedBackupStorageRedundancy; you cannot retrofit geo-redundancy onto already-taken LRS/ZRS backups.

5. Define RPO and RTO for Azure SQL backups, with realistic numbers. RPO (max data loss): for in-region PITR it’s effectively the log-backup interval (minutes); for geo-restore up to about 1 hour because geo-replicated backups lag asynchronously. RTO (recovery time) is driven mostly by database size — the platform materialises a new database from backups, so larger means longer.

6. What backup types does Azure SQL take automatically, and how do they enable second-level PITR? Full (~weekly), differential (~every 12–24 h), and transaction-log (~every 5–10 min). A PITR replays the most recent full + differential before the target time, then rolls the log forward to the exact second — the frequent log backups are what make any-second recovery possible.

7. Your LTR restore shows no backups for the date you need. Why? The LTR policy was configured after the backup you want — LTR only retains weekly fulls taken while the policy is active and cannot retroactively keep an earlier point. Configure LTR when you create a compliance-bound database, not when the auditor asks.

8. How does backup storage redundancy relate to cost and to geo-restore? It sets where backups are stored: LRS (one datacentre), ZRS (zones), GRS (paired region), GZRS (zones + paired region). GRS/GZRS cost more per GB but are the only options that permit geo-restore; LRS/ZRS are cheaper but keep no cross-region copy.

9. Can you restore a database after it’s been deleted? What’s the limit? Yes — within the deleted database’s backup retention window (az sql db list-deleted then az sql db restore --deleted-time). Past that window the PITR backups are gone, but LTR backups survive the deletion and can still be restored — which is why LTR matters for anything you can’t lose.

10. Why might you NOT cut over wholesale to a point-in-time-restored database? Because legitimate transactions may have committed after the incident you restored past, and a wholesale swap to the pre-incident state would discard them. Restore to just before the incident, then merge only the corrupted rows back into the live database, preserving the good writes.

11. Backups aren’t meeting your tight RTO for a large database. What do you reach for instead? RTO scales with size, so backups alone won’t give fast recovery. For low RTO and very low RPO use active geo-replication / failover groups, which keep a hot secondary you fail over to in seconds — a different mechanism, layered above backups.

12. Where do automated backups physically live, and do you manage that storage? In Azure-managed backup storage with the chosen redundancy (GRS by default). You do not manage the storage account, schedule the jobs, or run the backups — the platform does. You control only the retention (PITR window + LTR policy) and the redundancy (set at creation).

These map to DP-300 (Azure Database Administrator Associate) — plan and implement data platform resources; configure and manage automatic, point-in-time and long-term backup/restore — and to the data-platform portions of AZ-305 (Solutions Architect) for designing business-continuity and DR. The RPO/RTO and redundancy reasoning also touches AZ-104. A compact cert mapping:

Quick check

1. A bad batch job corrupted data 90 minutes ago and you want it back exactly as it was at the moment before the job ran. Which mechanism, and what kind of object does the restore produce?
2. True or false: setting the PITR window to its maximum (35 days) is a valid way to satisfy a 1-year compliance retention requirement.
3. Your team set a database’s backup storage redundancy to LRS to save cost. What recovery capability did they just lose, and when will they find out?
4. An LTR restore shows no backup available for last March, even though you “have LTR turned on.” Give the most likely reason.
5. Why is it sometimes wrong to simply repoint your application to a point-in-time-restored database, and what should you do instead?

Answers

1. Point-in-time restore (PITR), to the timestamp just before the job ran (well within the window). It produces a new database alongside the original — restores never overwrite in place, so you validate the new DB and then cut over.
2. False. PITR maxes at 35 days — about five weeks, not a year. Compliance retention of a year (or more) requires long-term retention (LTR), which keeps backups up to 10 years and survives deletion of the database.
3. They lost geo-restore — LRS keeps no copy in the paired region, so there is nothing to restore from if the region is lost. They will find out during an actual regional outage, the worst possible time, because there is no warning in normal operation. Fix: use GRS/GZRS (and it can’t be retrofitted to existing backups).
4. The LTR policy was configured after last March, so no weekly full from that period was ever archived. LTR only retains backups taken while the policy was active — it cannot retroactively keep an earlier point.
5. Because legitimate writes may have committed after the incident you restored past; a wholesale cutover to the pre-incident state would discard them. Instead, restore to just before the incident and merge only the corrupted rows back into the live database, preserving the good writes.

Glossary

• Automated backups — the full (~weekly), differential (~every 12–24 h) and transaction-log (~every 5–10 min) backups Azure SQL takes continuously from database creation; you manage retention and redundancy, not the jobs. The log backups are what make second-level PITR possible.
• Point-in-time restore (PITR) — restoring a database to any second within the retention window by replaying full + differential + log.
• PITR retention window — the rolling 1–35-day period (default 7) within which PITR can reach; set per database.
• Long-term retention (LTR) — a policy keeping weekly/monthly/yearly full backups up to 10 years; discrete points that survive deletion of the source database.
• Geo-restore — restoring from the geo-replicated backup copy in the paired region; recovery for a regional outage; requires GRS/GZRS.
• Backup storage redundancy — where backups are stored: LRS (one datacentre), ZRS (zones in-region), GRS (paired region, default), GZRS (zones + paired region).
• RPO (Recovery Point Objective) — the maximum data loss in a recovery; minutes for in-region PITR, up to ~1 hour for geo-restore.
• RTO (Recovery Time Objective) — how long recovery takes; driven mainly by database size.
• Deleted-database restore — bringing back a dropped database within its backup retention window (az sql db list-deleted + restore).
• Restore target / destination database — the new database a restore creates; restores never overwrite the original in place.
• earliestRestoreDate — the oldest point-in-time a database can currently be restored to, bounded by its retention window.
• Transparent Data Encryption (TDE) — at-rest encryption whose state carries through restores; with customer-managed keys, the key must be available in the target before a geo-restore completes.
• Failover group / active geo-replication — a hot continuously-replicated secondary for low-RPO, fast failover; layered above backups and beyond this article’s scope.

Next steps

You can now configure and test all three Azure SQL recovery tools and pick the right one for any incident. Build outward:

• Next: Business Continuity & Disaster Recovery: RTO & RPO Fundamentals — set the targets that drive every window and redundancy choice above.
• Related: Azure SQL DTU vs vCore Purchasing Models Explained — how the purchasing model shapes backup storage billing and sizing.
• Related: Azure SQL Serverless: Auto-Pause & Compute Autoscaling — the cost-efficient tier used in the lab, and how auto-pause interacts with backups.
• Related: Azure Storage Redundancy: LRS, ZRS, GRS & RA-GRS Explained — the redundancy model that backup storage mirrors, in depth.
• Related: Troubleshooting Azure SQL Database: Connectivity, Timeouts, Throttling & Blocking — diagnose the live-database issues that sit upstream of needing a restore.