Configuring Azure SQL Database backups with Terraform

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 month
monthly_retention = "P1Y" → keep monthly snapshots 1 year
yearly_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.