
Every so often, a dev accidentally nukes the main database—one rogue and BANG!, all the data is “gone, reduced to ashes” . If you’ve ever face-palmed at your own typo (or watched your carefully seeded data disappear in a puff of digital smoke), you know why I’m such a fan of Azure SQL’s built-in backups.
In this post, I’ll share:
- What Azure SQL backs up for you out of the box
- How I configure those backups with Terraform
- My preferable configurations for various environments
Why I Rely on Azure’s Automatic Backups
Azure SQL Database takes care of backups so you don’t have to manage them yourself:
- Point-in-Time Restore (PITR):
Every week there’s a full backup and, by default, differential backups every 12 hours. Azure retains these for a window I choose (up to 35 days), which lets me restore to any second within that period. It’s a safety net against accidental deletes or data corruption. - Long-Term Retention (LTR):
Azure also snapshots my database on a weekly, monthly, and yearly cadence, storing those in geo-redundant storage. I use this for compliance, audits, and in case I ever need to recover data that’s older than my PITR window.
How I Configure Backups in Terraform
Here’s the core of my Terraform config for an Azure SQL database:
resource "azurerm_mssql_database" "sqldb" {
# … other database settings …
# 1. Point-in-Time Restore (PITR)
short_term_retention_policy {
retention_days = 7 # I keep PITR backups for 7 days
backup_interval_in_hours = 12 # Differential backups every 12h
}
# 2. Long-Term Retention (LTR)
long_term_retention_policy {
weekly_retention = "P1M" # Weekly backups kept for 1 month
monthly_retention = "P1Y" # Monthly backups kept for 1 year
yearly_retention = "P7Y" # Yearly backups kept for 7 years
}
}
1. Tuning My PITR Settings
retention_days
(1–35):
I pick this based on how far back I might need to restore. In production I often use 7 days; for mission-critical apps I bump that to 35. In dev/test I’ll drop it to 3 or even 1 to save cost.backup_interval_in_hours
:
I leave this at the default (12 hours). Azure manages the full-vs-differential schedule automatically, and it’s almost never worth tweaking unless you have a very specific SLT requirement.
My PITR Examples
- Dev/Test (cost-sensitive):
short_term_retention_policy { retention_days = 3 backup_interval_in_hours = 12 }
- Standard Prod:
short_term_retention_policy { retention_days = 7 backup_interval_in_hours = 12 }
- Critical Prod (max window):
short_term_retention_policy { retention_days = 35 backup_interval_in_hours = 12 }
2. Defining My LTR Strategy
For long-term needs, I choose ISO-8601 durations:
weekly_retention = "P1M"
→ keep weekly snapshots 1 monthmonthly_retention = "P1Y"
→ keep monthly snapshots 1 yearyearly_retention = "P7Y"
→ keep yearly snapshots 7 years
You can adjust these to match your own business/compliance rules:
long_term_retention_policy {
weekly_retention = "P3M" # 3 months of weekly backups
monthly_retention = "P5Y" # 5 years of monthly backups
yearly_retention = "P10Y" # 10 years of yearly backups
}
3. Storing Backups in Geo-Redundant Storage
LTR backups land in a storage account I configure like this:
resource "azurerm_storage_account" "backup_storage" {
name = "sqldbbackups"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
account_tier = "Standard"
account_replication_type = "GZRS" # Zone- and geo-redundant
blob_properties {
delete_retention_policy { days = 14 } # Prevent accidental deletes
}
}
resource "azurerm_storage_management_policy" "mgmt_policy" {
storage_account_id = azurerm_storage_account.backup_storage.id
rule {
name = "expire-old-backups"
enabled = true
filters { blob_types = ["blockBlob"] }
actions {
base_blob {
delete_after_days_since_creation_greater_than = 3653 # ~10 years
}
}
}
}
- I use GZRS to protect against both zone and region failures.
- The delete retention policy stops accidental or malicious deletions.
- A lifecycle policy automatically purges blobs older than I need.
Locking Down and Securing My Backups
I also add these protections:
- Management Locks
resource "azurerm_management_lock" "db_lock" { name = "sqldb-lock" scope = azurerm_mssql_database.sqldb.id lock_level = "CanNotDelete"}
This guardrail protects against accidentally destroying the database or storage account. - Security Alert Policies on the SQL server, with audit logs pointed at the same geo-redundant backup storage, getting threat alerts and a centralized log store.
- (Optional) Immutability policies if you need true WORM protection for compliance.
Putting It All Together
Scenario | PITR Window | LTR Weekly | LTR Monthly | LTR Yearly |
---|---|---|---|---|
Dev/Test | 3 days | 1 week | — | — |
Standard Production | 7 days | 1 month | 1 year | 7 years |
Highly Regulated (Finance/Healthcare) | 35 days | 3 months | 5 years | 10 years |
By leveraging Azure’s built-in backup engine and a few Terraform blocks, I’ve automated a comprehensive backup strategy that balances cost, recovery needs, and compliance.