When managing data in Azure SQL Database, it's easy to assume that the built-in backup and restore features are enough to meet all your recovery needs. However, the default retention plan—one week of point-in-time restore—is designed with development and testing environments in mind, not for production workloads or long-term data governance. In this article, we'll explore why that's a problem and walk through a solution that supports robust long-term retention using Azure’s built-in capabilities.
The Problem: Default Backup Retention is Inadequate for Production
By default, Azure SQL provides 7 days of point-in-time restore (PITR). While this might suffice for development environments or short-term recovery needs, it falls short for critical production systems, especially those subject to regulatory compliance, audit requirements, or internal business continuity standards.
Without a longer-term backup strategy, you risk losing access to older backups, which could be a serious liability in scenarios involving data corruption, ransomware, legal inquiries, or accidental deletions that aren't discovered within a week.
The Solution: Configure a Long-Term Retention Policy
To protect your Azure SQL data beyond the short 7-day window, you need to configure Long-Term Retention (LTR). Azure SQL allows you to store full backups in an Azure Recovery Services vault or directly through automated LTR settings, enabling you to retain backups for weeks, months, or even years.
A Practical Long-Term Retention Strategy
A sound LTR policy is typically a tiered one, designed to balance storage cost and compliance needs:
- Weekly Backups: Retain at least the most recent 5 weeks. Some teams opt for 12 to cover each week in a fiscal quarter.
- Monthly Backups: Retain one full backup per month for 12 months.
- Yearly Backups: Retain one full backup per year for 5+ years, depending on your business and regulatory requirements.
In Azure, you can configure this using the Azure Portal, PowerShell, or the Azure CLI. Notably, for yearly backups, Azure allows you to choose which weekly backup should be promoted and retained as the annual copy (e.g., the backup taken during the first week of January).
Example: Setting LTR via Azure CLI
Here's a sample Azure CLI command to set a long-term retention policy for a specific database:
az sql db ltr-policy set \
--resource-group myResourceGroup \
--server mySqlServer \
--database myDatabase \
--weekly-retention P5W \
--monthly-retention P12M \
--yearly-retention P5Y \
--week-of-year 1
This command configures:
- Weekly backups retained for 5 weeks
- Monthly backups retained for 12 months
- Yearly backups retained for 5 years, using the first week of the year
Lessons Learned: Don't Rely on Defaults
The biggest lesson here is that backup policies in Azure SQL aren't "set and forget"—they need to be deliberately configured based on your environment's criticality and compliance standards. Each database in your Azure SQL instance can be configured individually, so you can tailor retention strategies to the importance of each workload.
Take time to:
- Review your backup needs based on your Recovery Point Objectives (RPO) and compliance mandates
- Implement a retention plan that protects your data for the duration your organization requires
- Periodically audit your backup policies to ensure they’re still aligned with current business needs
Final Thoughts
Azure SQL provides the tools to safeguard your data, but it's up to you to implement a strategy that meets your organization’s standards. Long-term backup retention isn’t just about compliance—it’s a cornerstone of responsible data management. By setting up a solid LTR policy today, you can ensure recoverability and peace of mind for the long haul.