SQL long-term retention, often shortened to LTR, is the Azure SQL backup policy for keeping database backups far longer than the normal short-term restore window. It is meant for audit, compliance, legal, and business-history needs, not for everyday point-in-time recovery. You choose weekly, monthly, and yearly retention periods, and Azure keeps selected full backups in long-term storage. Later, you can restore one of those backups as a new database. The policy needs ownership because old data can become both valuable evidence and a cost or privacy risk.
Microsoft Learn describes Azure SQL long-term retention as a policy that keeps selected automated full backups in redundant Azure Blob Storage for up to 10 years. It extends recovery beyond short-term retention and supports restoring retained backups as new databases.
In Azure architecture, SQL long-term retention sits on top of Azure SQL automated backups. The database remains under a logical server, while selected full backups are copied to redundant Azure Blob Storage according to the LTR policy. Operators manage LTR through Azure portal, CLI, PowerShell, REST, and database backup views. It connects to compliance retention, backup immutability, restore permissions, RBAC, subscription scope, regional backup storage, dropped-server recovery scenarios, and FinOps tracking. LTR is separate from short-term retention, failover groups, geo-replicas, and BACPAC exports.
Why it matters
SQL long-term retention matters because normal restore windows are too short for many real obligations. A tax audit, contract dispute, patient record request, financial investigation, or engineering regression might require data from years ago. Without LTR, teams may discover that the database was recoverable for weeks, not years. With LTR, the organization can prove retention intent and restore historical backups when needed. It also forces governance decisions: how long data should survive, who can restore it, whether backups should be immutable, how privacy deletion requests are handled, and how costs are assigned to the business that requires retention. It keeps recovery obligations tied to evidence, not memory.
⌁
Where you see it
Signals, screens, and Azure surfaces where this term usually becomes operational.
Signal 01
In the Azure portal backup settings, long-term retention policies show weekly, monthly, yearly, and week-of-year values for a selected SQL database. for regulated databases.
Signal 02
In Azure CLI output, az sql db ltr-policy show returns the configured retention durations and immutability-related values for compliance evidence. during audit preparation. for compliance evidence reviews.
Signal 03
In restore workflows and backup inventories, retained LTR backups appear with timestamps, database identity, server context, location, and available restore actions. during recovery testing. before executive recovery briefings.
✦
When this becomes relevant
Specific situations where this term helps solve real Azure design, operations, migration, security, reliability, cost, or governance problems.
Retain Azure SQL Database backups for audit, tax, legal, or regulatory periods that exceed the normal short-term restore window.
Restore a historical backup as a new database for investigation without rolling the production database back in time.
Apply different weekly, monthly, and yearly retention periods based on data classification, application criticality, and business owner requirements.
Enable immutability for LTR backups where compliance requires protection against accidental or malicious backup modification or deletion.
Inventory databases with missing or excessive LTR policy so compliance risk and long-term storage cost are visible before an audit.
◆
Real-world case studies
Different enterprise-style examples that show the term being used to hit measurable objectives.
Case study 01
Aerospace supplier retains certification records
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
An aerospace parts supplier stored certification and inspection data in Azure SQL Database. Contracts required records to remain recoverable for seven years, but the live database only had short-term restore coverage.
🎯Business/Technical Objectives
Retain required backups for seven years without manual exports.
Restore historical data for auditors without touching production.
Assign retention cost to the certification business unit.
Prove policy configuration before the next supplier audit.
✅Solution Using SQL long-term retention
The platform team implemented SQL long-term retention on the certification database with yearly retention aligned to contract language and weekly retention for near-term investigations. Azure CLI set the LTR policy, exported policy output, and listed retained backups during quarterly controls testing. The team tagged the database with the owning business unit and documented restore commands that create a new isolated database for audit review. Access to restored databases required approval from compliance and engineering quality leaders. Finance received a retention cost line tied to the certification workload instead of generic platform storage.
📈Results & Business Impact
Audit preparation time dropped from six days of evidence gathering to one afternoon.
The first test restore completed in 74 minutes and did not interrupt production users.
Retention cost was forecast within 8 percent after backup inventory stabilized.
The supplier audit closed with no backup-retention finding for the Azure SQL system.
💡Key Takeaway for Glossary Readers
SQL long-term retention turns a contractual backup promise into a visible, testable, and restorable Azure policy.
Case study 02
Legal nonprofit restores historic case files
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A legal aid nonprofit needed database records from a closed housing-rights program after a class-action discovery request. The application was retired, but its Azure SQL database still had an LTR policy.
🎯Business/Technical Objectives
Find a backup from the requested quarter three years earlier.
Restore records into a locked-down review environment.
Avoid exposing unrelated client data to volunteer attorneys.
Remove the restored database after discovery production.
✅Solution Using SQL long-term retention
Operations used SQL long-term retention inventory to locate retained backups for the old database. Azure CLI listed available LTR backups by server and database, then restored the selected backup as a new database under a restricted logical server. The team applied firewall restrictions, Entra-only access, auditing, and query views that limited attorneys to the requested matter. Every command output was attached to the discovery evidence file. After the legal team exported approved records, the temporary database was deleted and the cleanup evidence was reviewed by the privacy officer.
📈Results & Business Impact
The requested backup was found in 22 minutes instead of reconstructing files from paper archives.
Discovery review began the next business day, beating the court deadline by 11 days.
No current client database was exposed during the restore or legal review process.
Temporary resources were removed the same week, preventing long-lived sensitive copies.
💡Key Takeaway for Glossary Readers
SQL long-term retention is valuable only when restore access, isolation, privacy, and cleanup are governed as carefully as retention duration.
Case study 03
Water authority right-sizes excessive backup retention
Scenario, objectives, solution, measured impact, and takeaway.
📌Scenario
A regional water authority discovered that every Azure SQL database had ten-year retention after a rushed cloud migration. Low-value telemetry summaries were costing more to retain than the data justified.
🎯Business/Technical Objectives
Classify databases by regulatory and operational retention need.
Reduce unnecessary long-term backup storage cost.
Keep billing, permit, and safety records protected for audit periods.
Create an annual review process for LTR policy drift.
✅Solution Using SQL long-term retention
The data platform team used SQL long-term retention policy output from Azure CLI to build an estate-wide inventory. Databases were grouped into safety-critical, billing, reporting, and ephemeral analytics classes. Billing and safety databases kept multi-year LTR, while telemetry summary databases moved to shorter retention or no LTR after approval. The team documented that policy changes affect future backups and separately reviewed existing retained backups. A workbook displayed retention policy, classification tag, business owner, and estimated storage trend for leadership review.
📈Results & Business Impact
Projected long-term backup storage cost fell by 41 percent over the following fiscal year.
All safety-critical and billing databases retained documented compliance coverage.
Thirty-eight low-value databases moved off blanket ten-year retention with owner signoff.
Annual LTR review became part of the authority’s normal data governance calendar.
💡Key Takeaway for Glossary Readers
SQL long-term retention should be deliberate; the right policy protects evidence while avoiding years of unnecessary storage liability.
Why use Azure CLI for this?
With ten years of Azure engineering behind me, I use Azure CLI for SQL long-term retention because retention policy is easy to overlook and painful to audit manually. CLI lets me show the current policy, set weekly, monthly, and yearly durations, enable backup immutability where appropriate, list retained backups, and export evidence across many databases. Retention changes need precise parameters, not vague portal memory. CLI also supports compliance sweeps: I can find databases with missing LTR, compare policies to data classification, and capture JSON for auditors. When a restore is requested, CLI helps locate the retained backup before anyone makes promises.
CLI use cases
Show the LTR policy for a database and export weekly, monthly, yearly, and immutability fields for an audit evidence package.
Set or update LTR durations when a database moves into a regulated data classification or a contract requires longer retention.
List available long-term retention backups before approving a legal, finance, or incident-response restore request.
Restore a retained backup into a new isolated database for investigation without changing the live production database.
Find databases with no LTR policy or excessive retention by looping across servers and comparing policy output to tags.
Before you run CLI
Confirm subscription, resource group, logical server, database name, location, data classification, retention requirement, and output format.
Verify RBAC permissions to read, set, restore, or delete long-term retention backups, especially when dropped servers or subscription scope are involved.
Review cost, privacy, immutability, and legal implications before extending retention or restoring old data into a temporary database.
Check whether policy changes affect only future backups, and confirm the business owner understands existing retained backups may keep prior settings.
What output tells you
Weekly, monthly, yearly, and week-of-year fields show which backups Azure will retain and how long each class survives.
Immutability fields indicate whether retained backups have stronger protection against modification or deletion for compliance evidence.
Backup listings reveal available restore points, locations, database identity, and timestamps that determine whether a request can be satisfied.
Restore command output identifies the new database target, provisioning state, and resource IDs needed for access control and cleanup tracking.
Mapped Azure CLI commands
SQL long-term retention CLI
az sql db ltr-policy show --resource-group <resource-group> --server <server> --name <database>
az sql db ltr-policydiscoverDatabases
az sql db ltr-policy set --resource-group <resource-group> --server <server> --name <database> --weekly-retention P12W --monthly-retention P12M --yearly-retention P5Y --week-of-year 26
az sql db ltr-policyconfigureDatabases
az sql db ltr-backup list --location <location> --server <server> --database <database>
az sql db ltr-backupdiscoverDatabases
az sql db ltr-backup restore --dest-database <new-database> --dest-resource-group <resource-group> --dest-server <server> --backup-id <backup-resource-id>
az sql db ltr-backupprotectDatabases
az sql db ltr-policy set --resource-group <resource-group> --server <server> --name <database> --weekly-retention P1W --make-backups-immutable Enabled
az sql db ltr-policyconfigureDatabases
Architecture context
As an architect, I align SQL long-term retention with data classification and legal obligations, not with a blanket default. Some databases need seven or ten years of retention; others should expire quickly to reduce privacy exposure. The architecture includes the database, logical server, backup storage redundancy, LTR policy, immutability choice, restore permissions, evidence capture, and cleanup process for retained backups. I also separate LTR from disaster recovery: failover groups help continuity, point-in-time restore handles recent mistakes, and LTR handles older evidence. A mature design documents who owns retention, who pays for it, and who can approve restoration of old data.
Security
Security impact is direct because long-term backups preserve sensitive data long after the live database changes. A restored backup can expose old customers, deleted accounts, historic secrets, or regulated records if access is loose. Operators should restrict who can view, restore, or delete LTR backups, use RBAC carefully, review subscription-scope permissions for dropped-server backups, and consider backup immutability for audit environments. Encryption and Azure-managed storage protect the backup platform, but governance still matters. Retention also intersects with privacy obligations; keeping data for years should be justified, documented, and matched to legal requirements. Immutable retention further reduces privileged deletion risk during investigations.
Cost
Cost impact is direct because retained backups consume long-term storage for months or years. The more databases, weekly backups, monthly backups, yearly backups, redundancy choices, and immutability requirements you keep, the more cost accumulates. Restoring old backups also creates new databases that can generate compute, storage, logging, and private endpoint costs if left running. FinOps teams should attach retention cost to the compliance or business owner that requires it, not hide it in platform overhead. Practical controls include policy reviews, database classification, backup inventory, cleanup of restored databases, and avoiding ten-year retention for low-value data. Documented owners prevent old backup policies from quietly becoming permanent spend.
Reliability
Reliability impact is strong for historical recovery but different from high availability. LTR does not keep the application online during an outage, and it is not a quick failover mechanism. Its value is the ability to restore selected historical full backups as new databases when recent restore windows are insufficient. Reliable use requires policies to be configured before the need arises, retained backups to be discoverable, and restore procedures to be tested. Teams should confirm retention after database moves, server deletion plans, tier changes, and governance updates. A policy that exists only on paper is not a recovery capability. Restore rehearsals prove the archive is usable, not merely configured.
Performance
Performance impact on the live workload is usually low because LTR uses automated full backups copied in the background. The bigger performance concern appears during restore and investigation. Restoring a large retained backup can take time, create a database that needs enough compute and storage, and require indexing or query checks before analysts can use it. Operators should not promise instant access to multi-terabyte historical data. LTR policy review also improves operational performance: teams can quickly find retained backups, choose the right restore point, and avoid wasting time on databases that never had long-term retention configured. Scheduled restore tests keep recovery work predictable for auditors and engineers.
Operations
Operators manage SQL long-term retention by showing policies, setting retention durations, listing retained backups, restoring requested backups, and deleting backups only when policy allows. Runbooks should include database name, server, resource group, location, policy values, immutability status, backup timestamps, requester approval, restore target, and validation steps. Regular reviews compare LTR policy against data classification and application ownership. During incidents or legal requests, operators must avoid restoring sensitive historical data into an insecure environment. After restore, they should tag temporary databases, monitor costs, restrict access, and schedule cleanup when the investigation closes. Calendar-based reviews keep retention aligned with changing legal requirements.
Common mistakes
Assuming point-in-time restore covers multi-year audit needs, then discovering the short-term window expired long before the request arrived.
Applying ten-year retention to every database without checking data classification, privacy obligations, storage cost, or business owner approval.
Changing LTR policy and assuming old retained backups automatically inherit the new retention period when policy changes mainly affect future backups.
Restoring historical data into an open development environment, exposing records that production access controls had already restricted.