Site Reliability

Tune Azure SQL backup retention and long-term retention

One capability that decides how far back you can recover an Azure SQL database: set the point-in-time restore window, keep backups geo-redundant so a regional outage cannot take your recovery with it, and add long-term retention where compliance or recoverability needs years, not days.

14 min·10 sections·AZURE

Last reviewed 18 June 2026

Backup retention and recovery: the basics

What actually decides how far back you can restore an Azure SQL database?

Azure SQL Database backs every database up automatically, but how recoverable a database really is comes down to three settings that are easy to leave on their defaults. The first is the point-in-time restore window: the short-term retention policy, which can be set from 1 to 35 days and defaults to 7. Inside that window you can restore to any second. Outside it, the backups are simply gone. The second is where those backups live: by default they sit in geo-redundant storage replicated to the paired region, but a database can be created or changed to use locally or zone-redundant storage, which is cheaper and silently disables geo-restore. The third is long-term retention, which is off until you turn it on and lets you keep weekly, monthly and yearly full backups for up to 10 years.

These read as three unrelated knobs, but they are one capability: how far back, and from where, can you recover when something goes wrong. A seven-day window is fine for a fat-finger delete noticed the same morning. It is no help at all against a logical corruption or a bad migration discovered three weeks later, or an auditor who needs the year-end state of a financial database from four years ago. Each gap is a different shape of the same risk, the inability to recover to the point you actually need.

Most of this is drift rather than intent. A database provisioned with the default 7-day window because nobody changed it, a cost-saving module that set locally redundant backup storage and quietly removed the ability to recover in a second region, a long-term retention policy that was never configured because no recovery requirement was ever written down. The work is to decide, per database, how far back you need to be able to recover and from where, then set retention, storage redundancy and long-term retention to match, rather than inheriting whatever the defaults happened to be.

In this lesson you will learn how Azure SQL Database expresses recoverability through three settings: the short-term point-in-time restore window, backup storage redundancy, and long-term retention. You will see how to find every database whose retention does not match its importance, and how to set retention, redundancy and long-term retention deliberately rather than inheriting the defaults, all without affecting the running workload.

Fun fact

The seven-day cliff

The most common backup surprise in Azure SQL is not a missing backup, it is a backup that expired. Point-in-time restore defaults to a 7-day window, and inside it you can recover to any single second. The cliff is the edge: a logical corruption or a bad data migration is often not noticed for two or three weeks, by which time every backup that pre-dates the damage has rolled off. The backups were always there and always working. The window was simply shorter than the time it took to discover the problem, which is exactly the failure long-term retention and a longer point-in-time window exist to prevent.

Finding databases whose retention does not match their importance

Priya runs reliability for a fintech platform heading into an audit and a disaster-recovery exercise at the same time. The team has dozens of Azure SQL databases, most provisioned over three years by different squads, and nobody is sure which ones still run on the default 7-day window or on local-only backup storage.

Rather than open each database in the portal, Priya starts by reading the short-term retention policy and the backup storage redundancy straight from the control plane, so the databases whose recoverability does not match their importance can be separated from the ones that are already configured deliberately.

Read the point-in-time restore window for a database, then check where its backups are stored. A short window plus local-only storage is the least recoverable combination.

$ az sql db str-policy show -g rg-fin-prod -s sql-fin-prod -n ledger --query retentionDays az sql db show -g rg-fin-prod -s sql-fin-prod -n ledger --query currentBackupStorageRedundancy -o tsv

Local

# 'ledger' is a financial database on a 7-day window with LOCAL-only backups.

# A corruption found after a week is unrecoverable, and a regional outage takes the backups too.

A short restore window plus locally redundant backup storage is the least recoverable configuration. On a database that matters, fix the window and the redundancy first, then add long-term retention.

How Azure SQL backup retention and recovery actually workdeep dive

Azure SQL Database takes full, differential and transaction-log backups continuously, and the short-term retention policy decides how long they are kept: any value from 1 to 35 days, defaulting to 7. Within that window you can restore to any single second, because the service replays log backups to the moment you choose. The differential backup frequency, either every 12 or 24 hours, is a secondary knob that affects restore granularity and storage. Extend the window and you extend the period over which a late-discovered problem is still recoverable, at the cost of more backup storage.

Where those backups physically live is a separate property, the backup storage redundancy, and it is the one that quietly governs disaster recovery. Geo-redundant storage, the default, replicates backups to the Azure paired region, so geo-restore can rebuild the database elsewhere if the primary region has an outage. Geo-redundant replication is asynchronous, so the geo-restore recovery point can lag the primary by up to an hour. Switch a database to locally or zone-redundant storage and the backup-storage bill drops, but geo-restore is disabled: the backups never leave the region, so a regional outage takes your recovery path with it. You can change the redundancy on an existing database, but it is not instant and can take up to 48 hours to apply.

Long-term retention is a third, independent policy. It copies the weekly, monthly and yearly full backups into separate storage and keeps them for as long as you specify, up to 10 years, expressed as ISO 8601 durations such as P12W, P12M and P10Y plus the week of the year for the yearly backup. It exists for recoverability and compliance horizons that the 35-day point-in-time window can never reach, and the backups can optionally be made immutable so they cannot be deleted before their retention expires. Setting all three deliberately is the difference between a database that is backed up and a database you can actually recover to the point, and from the place, you need.

What is the impact of leaving retention on its defaults?

The direct impact is an outage you cannot fully recover from. A database on a 7-day window, hit by a logical corruption or a bad migration found three weeks later, has no clean backup left to restore: every backup that pre-dates the damage has already expired. The recovery stops being a restore and becomes hours or days of manual data reconstruction, with the business down or running on degraded data the whole time. The backups worked perfectly. The window was just shorter than the time it took to notice the problem.

The second-order impact is regional blast radius. A database whose backups were switched to locally or zone-redundant storage to save money has no geo-restore: if the primary region has a bad day, the backups are stuck in the same region as the failure, so the outage and the recovery path go down together. Geo-redundant storage is what turns a regional incident from an unrecoverable event into a geo-restore in the paired region, accepting the documented recovery-point lag of up to an hour.

On the compliance and recoverability side, many regulated workloads must be able to produce a database's state from years ago: a year-end financial position, a record retained for a statutory period. The 35-day point-in-time window cannot reach that horizon, and long-term retention is the only mechanism that can, with immutability available where the data must be provably unaltered. A retention posture set deliberately per database is both the recovery insurance and the audit artefact.

How do you set retention and recovery deliberately?

Work the capability as one loop rather than turning every knob to maximum. The order matters: decide the recovery requirement per database first, so you buy retention where downtime is expensive and not where it is not.

1. Inventory every database by its current recoverability

For each database, read the point-in-time restore window (short-term retention), the backup storage redundancy, and whether long-term retention is configured. Treat this inventory as the source of truth. A short window plus locally redundant storage with no long-term retention is the least recoverable combination and the first to look at.

2. Attach a recovery requirement to each database

Decide, per database, how far back you need to recover and from where. A revenue-bearing system needs a long window, geo-redundant storage and probably long-term retention. A database that can be rebuilt from source in an afternoon is genuinely fine on the default 7-day, geo-redundant setting. Size retention to the cost of downtime, not to a single estate-wide rule.

3. Set the window and the redundancy, then add long-term retention

Extend the short-term window towards 35 days where late discovery is a real risk, keep or restore geo-redundant backup storage so geo-restore stays available, and configure long-term retention for the weekly, monthly and yearly states that compliance or recoverability needs. Remember a redundancy change can take up to 48 hours to apply, so plan it ahead of any audit or DR exercise.

4. Make the standard repeatable

Bake the chosen retention, redundancy and long-term retention into the Bicep or Terraform that provisions databases, so new databases inherit a deliberate posture rather than the bare defaults. Where data must be provably unaltered for its full retention, enable immutable long-term backups so they cannot be deleted before expiry.

# 1. Extend the point-in-time restore window to 35 days on a database that matters.
#    Valid retention is 1-35 days; diff backup frequency is 12 or 24 hours.
az sql db str-policy set \
  --resource-group rg-fin-prod \
  --server sql-fin-prod \
  --name ledger \
  --retention-days 35 \
  --diffbackup-hours 12

# 2. Restore geo-redundant backup storage so geo-restore stays available.
#    Geo-restore is disabled on Local/Zone redundancy. Change can take up to 48h.
az sql db update \
  --resource-group rg-fin-prod \
  --server sql-fin-prod \
  --name ledger \
  --backup-storage-redundancy Geo

# 3. Add long-term retention for the states compliance needs (up to 10 years),
#    optionally immutable so backups cannot be deleted before they expire.
az sql db ltr-policy set \
  --resource-group rg-fin-prod \
  --server sql-fin-prod \
  --name ledger \
  --weekly-retention P12W \
  --monthly-retention P12M \
  --yearly-retention P7Y \
  --week-of-year 1 \
  --make-backups-immutable Enabled

Quick quiz

Question 1 of 5

A production Azure SQL database is left on the default short-term retention. How far back can you restore it to a point in time, and what is the risk?

Keep learning

Go deeper on how Azure SQL Database expresses recoverability across the point-in-time window, backup storage redundancy and long-term retention.

You can now treat Azure SQL backup retention as one recoverability capability rather than three defaults: inventory each database's restore window, backup storage redundancy and long-term retention, attach a recovery requirement based on what its downtime would cost, set the window and redundancy deliberately, and add long-term retention where compliance or recoverability needs years. Backups are not the same as recovery, and the gap between them is the settings you choose.

Back to the library

Backup retention and recovery: the downtime-cost view

A small, bounded backup-storage cost set against the cost of an outage you cannot recover from

This is one of the rare reliability levers where you can put both sides of the trade on the same page. Extending the point-in-time restore window or turning on long-term retention adds backup-storage cost, and Azure prices it: short-term retention beyond the free allowance and long-term retention are billed on the backup storage consumed. The numbers are real but bounded, and you can forecast them from database size and the retention you choose.

On the other side sits the cost of an outage you cannot recover from. A database that can only be restored seven days back, hit by a corruption found on day twenty, is not a backup-storage line item, it is hours or days of downtime, lost transactions and manual reconstruction. Keeping backups geo-redundant rather than local is a small storage premium against the alternative of having no recovery path at all if the primary region has a bad day. Frame each database as: what does an hour of this being down cost us, and does our retention actually cover the failure modes that cause that downtime?

The arithmetic favours resilience for anything that matters. The recurring cost is a predictable, modest backup-storage charge. The avoided cost is the downtime, the lost data and the recovery labour of an outage where the backup you needed had already expired or sat only in a region that was also down.

This lesson is for the finance partner who needs to weigh the backup-storage cost of longer retention and geo-redundant storage against the cost of downtime and unrecoverable data. It covers what Azure actually bills for backups, why most of the cost is bounded and forecastable, and how to size retention per database against what an hour of its downtime is worth.

Fun fact

The seven-day cliff

How a finance partner frames the retention decision

Priya is the finance partner sitting with the reliability team before the audit. The instinct is not to wave through 'maximum retention everywhere', because long-term retention and an extended restore window both add backup-storage cost that scales with database size. The instinct is to ask the only question that sets the right number: what does an hour of each database being down, or a day of its data being unrecoverable, actually cost the business?

The team maps it out. The ledger database underpins customer transactions, so an outage there is immediate revenue loss and regulatory exposure, and it currently sits on a 7-day window with local-only backups, the cheapest and least recoverable setting. A reporting copy that can be rebuilt from source in an afternoon is genuinely fine on the default. The finance pack reframes the spend: 'The handful of revenue-bearing databases move to a 35-day window, geo-redundant storage and long-term retention for the year-end states the auditor needs. That is a forecastable backup-storage line. The rebuildable databases stay on the default at no added cost. We are buying recovery for the systems whose downtime is expensive and not paying for it where it is not.'

Why retention belongs on the resilience budget, not the cost-cutting list

The cost model here is genuinely two-sided, which is unusual for reliability work. Longer retention and long-term retention add backup-storage cost that scales with database size and the window you choose, and geo-redundant storage costs more than local. Those are real, recurring and, crucially, forecastable from size and retention. This is not a free control, and pretending it is would be wrong.

What makes the trade favour resilience for the databases that matter is the other side: the cost of downtime. An outage on a revenue-bearing database is lost transactions, idle staff, and a recovery effort measured in labour-hours, all of which dwarf a modest monthly backup-storage charge. The finance role is to attach a downtime cost to each database, then size retention and redundancy to match: pay the bounded storage premium where an hour of downtime is expensive, and stay on the cheap default where the database can simply be rebuilt. The metric for the review is not total backup spend, it is whether each database's retention matches the cost of it being unrecoverable.

What finance can do about the retention gap

Finance cannot set a retention policy, but it owns the framing that turns 'how much backup do we want?' into a per-database trade of bounded storage cost against the cost of downtime. Three levers.

1. Price both sides of the trade explicitly

Make clear that this is not a free control: an extended restore window, geo-redundant storage and long-term retention all add backup-storage cost that scales with database size, and Azure bills it. Then put the other side on the same page: the cost of an hour of downtime, the lost transactions, and the recovery labour for an outage where the needed backup had expired. Budget the retention premium as a forecastable line and weigh it against the downtime it removes, rather than treating backup spend as pure overhead to be minimised.

2. Size retention by the cost of downtime, not by a flat rule

A revenue-bearing database and a rebuildable reporting copy should not carry the same retention. Ask the team to attach a downtime cost to each database, then buy the long window, geo-redundant storage and long-term retention where that cost is high, and accept the cheap default where the database can be rebuilt from source. The metric that belongs on the review is whether each database's retention matches the cost of its being unrecoverable, not the total backup bill.

3. Require a written recovery requirement for the systems that matter

For every revenue-bearing or regulated database, ask for a recorded recovery requirement: how far back, from where, and for how long. The difference between 'we chose a 35-day window, geo-redundant storage and seven-year long-term retention because the auditor needs year-end states and an outage here costs us per hour' and 'we left it on the default' is exactly what makes the spend defensible and the posture auditable.

Quick quiz

Question 1 of 5

As the finance partner, what is the right starting assumption about the cost of extending retention and using geo-redundant storage?

Keep learning

Go deeper on how Azure SQL Database expresses recoverability across the point-in-time window, backup storage redundancy and long-term retention.

You have finished the finance view of backup retention. You know the cost model is two-sided: longer retention and geo-redundant storage add a real, forecastable backup-storage charge, set against the cost of downtime and unrecoverable data. Next time the retention question lands, you will size it per database by what an hour of its downtime is worth, buy recovery where that cost is high, accept the cheap default where a database can be rebuilt, and put both sides of the trade on the same page rather than minimising backup spend on reflex.

Back to the library

Backup retention and recovery: the headline

Whether the business can actually recover its databases to the point it needs, after the kind of outage that makes the news internally

Every database has a default backup configuration, and defaults are a decision nobody made. A seven-day restore window and the chance that some databases quietly run on local-only backup storage is a reliability posture set by inertia rather than by what the business can tolerate losing.

The leadership question is not 'do we have backups?' Azure takes those automatically. It is 'after a corruption found late, or a region having a bad day, can we recover the databases that run the business to the point we need, and how long does that take?' The defensible end state is that retention windows and storage redundancy are set deliberately per database against a recovery requirement, with long-term retention where compliance or recoverability demands it.

This is a cost decision, but a small and forecastable one, weighed against the cost of downtime. The trade is a modest, predictable backup-storage charge against the open-ended cost of an outage you cannot fully recover from. For the databases that matter, that is a trade leadership should want made on purpose, not left to a default.

A short read for the leader who needs to know what really decides whether a database can be recovered after a late-discovered failure or a regional outage, why the retention defaults are a decision nobody made, and what a defensible, deliberately set recovery posture looks like across the estate.

Fun fact

The seven-day cliff

What it looks like when recovery is set on purpose, not by default

After a competitor took a public, day-long outage that turned out to be an unrecoverable database, the board asked the obvious question at the next review: if that happened to us, could we get back, and how far back? The honest answer at the time was that it depended on the database, because retention and backup storage redundancy across the estate had been inherited from defaults rather than set against any recovery requirement.

The team's response was to stop treating backups as something Azure simply does and start treating recoverability as a deliberate posture. They listed every database, read its restore window and where its backups lived, and sorted them by what an outage would cost the business. The revenue-bearing systems moved to a longer point-in-time window, geo-redundant backup storage, and long-term retention for the states compliance required. The rebuildable systems stayed on the default on purpose. The next time the board asked, the answer had changed from 'it depends' to 'the systems that run the business can recover to the point we need, from a second region if we lose the first, and that is set deliberately, not by default.' That shift, from inheriting a recovery posture to choosing one, is the confidence signal this capability delivers.

Why this is a resilience decision leadership should own

Nearly every drawn-out database outage that becomes a leadership problem traces back to the same root cause: the recovery the business needed was not available, because the backup had expired or sat only in a region that was also down. Setting retention, storage redundancy and long-term retention deliberately is the capability that closes that gap, and it is bounded and forecastable rather than open-ended.

Unlike many resilience controls, this one has a real, recurring cost, but it is small and predictable next to the cost of the downtime it prevents. The leadership move is not to approve maximum retention everywhere, it is to make sure the databases whose downtime is expensive are recoverable to the point and from the place the business needs, and to accept the cheap default only where a database can genuinely be rebuilt. That is a deliberate trade, and it is exactly the kind leadership should want made on purpose.

The leadership move on recovery

The executive handle is not to approve each retention setting; it is to require that recoverability is set deliberately per database against the cost of downtime, with the revenue-bearing and regulated systems recoverable to the point and from the place the business needs.

1. Set the recovery posture against downtime cost, not a default

Ask for the databases that run the business to be sorted by what an outage costs, and for their restore window, backup storage redundancy and long-term retention to be set against that cost rather than inherited. A revenue-bearing database left on a 7-day, local-only setting is a recovery decision nobody made; making it deliberately is the whole point.

2. Ask whether you can recover from a second region

The question that separates a backup from a recovery is regional: if the primary region has a bad day, can the business-critical databases be restored elsewhere? That requires geo-redundant backup storage, with the documented recovery-point lag of up to an hour. Ask for confirmation that the systems that matter keep geo-redundancy and were not quietly switched to local-only storage to trim cost.

3. Treat the cheap default as a named, owned choice

Some databases are genuinely fine on the default 7-day, geo-redundant window because they can be rebuilt from source. That is a legitimate cost-saving, but it should be a recorded decision with an owner, not an accident. The goal is not maximum retention everywhere; it is that every database's recoverability matches what its downtime would cost, on the record.

Quick quiz

Question 1 of 5

At a board review you ask whether a long, unrecoverable database outage like a competitor's could happen to you. What end state should you be looking for?

Keep learning

Go deeper on how Azure SQL Database expresses recoverability across the point-in-time window, backup storage redundancy and long-term retention.

Two takeaways. Automated backups are not the same as recovery: how far back, and from where, you can restore is set by the retention window, the backup storage redundancy and long-term retention, and those are decisions, not defaults. And the defensible posture is not maximum retention everywhere; it is recoverability sized to the cost of downtime, with the business-critical databases recoverable to the point and from the region the business needs, set on purpose and on the record.

Back to the library

Part of the learning path Build in Azure resilience

Tune Azure SQL backup retention and long-term retention

Backup retention and recovery: the basics

The seven-day cliff

Finding databases whose retention does not match their importance

How Azure SQL backup retention and recovery actually workdeep dive

What is the impact of leaving retention on its defaults?

How do you set retention and recovery deliberately?

1. Inventory every database by its current recoverability

2. Attach a recovery requirement to each database

3. Set the window and the redundancy, then add long-term retention

4. Make the standard repeatable

Quick quiz

Keep learning

Backup retention and recovery: the downtime-cost view

The seven-day cliff

How a finance partner frames the retention decision

Why retention belongs on the resilience budget, not the cost-cutting list

What finance can do about the retention gap

1. Price both sides of the trade explicitly

2. Size retention by the cost of downtime, not by a flat rule

3. Require a written recovery requirement for the systems that matter

Quick quiz

Keep learning

Backup retention and recovery: the headline

The seven-day cliff

What it looks like when recovery is set on purpose, not by default

Why this is a resilience decision leadership should own

The leadership move on recovery

1. Set the recovery posture against downtime cost, not a default

2. Ask whether you can recover from a second region

3. Treat the cheap default as a named, owned choice

Quick quiz

Keep learning

Related site reliability lessons