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:

  1. What Azure SQL backs up for you out of the box
  2. How I configure those backups with Terraform
  3. 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

ScenarioPITR WindowLTR WeeklyLTR MonthlyLTR Yearly
Dev/Test3 days1 week
Standard Production7 days1 month1 year7 years
Highly Regulated (Finance/Healthcare)35 days3 months5 years10 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top