Configuring Azure SQL Database backups with Terraform

Ever so often, a developer accidentally nukes the prod database. All that precious data is “gone, reduced to ashes” . If you’ve ever horrified at your own mistake, you’ll know exactly 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 has two main types of backups:

• Point-in-Time Restore (PITR):
  Point-in-time restore is a self-service capability, giving the ability to restore an Azure SQL database from these backups to any point within a retention period . These backups are retained for 7 days for Basic, 35 days for Standard and 35 days for Premium tiers.
  PITR lets you restore to any second within that period, providing a crucial safety net against accidental deletes or data corruption.
  https://azure.microsoft.com/en-us/blog/azure-sql-database-point-in-time-restore/
• Long-Term Retention (LTR):
  Azure also allows you to store specified full SQL Database and SQL Managed Instance backups in redundant Azure Blob storage with a configurable retention policy of up to 10 years. 
  https://docs.azure.cn/en-us/azure-sql/managed-instance/automated-backups-overview
  https://docs.azure.cn/en-us/azure-sql/database/long-term-retention-overview

---

How I Configure Backups in Terraform

sql-server.tf

resource "azurerm_mssql_server" "sql" {
  name                = "mysqlserver"
  location            = "West Europe"
  resource_group_name = "rg"
  version             = "12.0"
  azuread_administrator {
    login_username              = azuread_group.perm_sql_admin.display_name
    object_id                   = azuread_group.perm_sql_admin.id
    tenant_id                   = "{tenant_id}"
    azuread_authentication_only = true
  }
  minimum_tls_version = "1.2"
  tags                = { description = "App SQL Server" }
  lifecycle {
    ignore_changes = []
  }
}

resource "azuread_group" "perm_sql_admin" {
    display_name            = format("pm-%s-administrator", "mysqlserver")
    description             = "Permission group granting administrative rights to SQL Server"
    security_enabled        = true
    members                 = [azuread_group.role_sql_admin.id]
    prevent_duplicate_names = true
    lifecycle {
        # apart from setting initially; do not flag changes in owners as state change
        ignore_changes = [owners]
    }
}

resource "azuread_group" "role_sql_admin" {
    display_name            = format("ra-%s-administrator", "mysqlserver")
    description             = "Role group which grants administrative access to SQL Server"
    security_enabled        = true
    members                 = [
        var.my_user_azure_object_id
    ]
    prevent_duplicate_names = true
    lifecycle {
        # apart from setting initially; do not flag changes in members and owners as state change
        ignore_changes = [members, owners]
    }
}

sql-db.tf

# SQL Database
resource "azurerm_mssql_database" "sqldb" {
  name           = "mydb"
  server_id      = azurerm_mssql_server.sql.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  max_size_gb    = 250 
  read_scale     = false
  sku_name       = "S1"
  min_capacity   = 0 #DTU
  zone_redundant = false
  tags           = {description = "SQL Database"}
  # Backup Configuration
  short_term_retention_policy {
      retention_days           = 7  # Retain PITR backups for 7 days
      backup_interval_in_hours = 12 # Perform differential backups every 12 hours (default)
}

  long_term_retention_policy {
      weekly_retention  = "P1M"  # Retain weekly backups for 1 month
      monthly_retention = "P1Y"  # Retain monthly backups for 1 year
      yearly_retention  = "P7Y"  # Retain yearly backups for 7 years
  }
}

resource "azurerm_management_lock" "sqldb_lock" {
    name       = "sqldb-mydb-lock"
    scope      = azurerm_mssql_database.sqldb.id
    lock_level = "CanNotDelete"
}

sql-backups.tf

## Geo-Redundant Storage for Backups of Databases
resource "azurerm_storage_account" "backup_storage" {
    name                            = "sqlbackupstor"
    resource_group_name             = "rg"
    location                        = "West Europe"
    account_kind                    = "StorageV2"
    account_tier                    = "Standard"
    account_replication_type        = "GZRS" # Could also do GRS to save money
    allow_nested_items_to_be_public = false
    min_tls_version                 = "TLS1_2"
    default_to_oauth_authentication = true
    access_tier                     = "Cool"
    https_traffic_only_enabled       = true
    # Enable blob immutability
    blob_properties {
        delete_retention_policy {
            days = 14
        }
    }
}

resource "azurerm_management_lock" "backup_storage_lock" {
    name       = "backup-storage-mydb-lock"
    scope      = azurerm_storage_account.backup_storage.id
    lock_level = "CanNotDelete"
}

## defender for storage
resource "azapi_resource_action" "defender-for-storage-backup" {
    type        = "Microsoft.Security/defenderForStorageSettings@2022-12-01-preview"
    resource_id = "${azurerm_storage_account.backup_storage.id}/providers/Microsoft.Security/defenderForStorageSettings/current"
    method      = "PUT"

    body = {
        properties = {
            isEnabled = true
            malwareScanning = {
                onUpload = {
                    isEnabled     = true
                    capGBPerMonth = 5000
                }
            }
            sensitiveDataDiscovery = {
                isEnabled = true
            }
            overrideSubscriptionLevelSettings = true
        }
    }

    depends_on = [
        azurerm_storage_account.backup_storage
    ]
}


## Lifecycle management policy
resource "azurerm_storage_management_policy" "mgmt-policy-sa-backup" {
    storage_account_id = azurerm_storage_account.backup_storage.id
    rule {
        name    = "delete-blobs-after-10-years"
        enabled = true
        filters {
            blob_types = ["blockBlob"]
        }
        actions {
            base_blob {
                delete_after_days_since_creation_greater_than = 3653
            }
        }
    }
}


# Data Source to Fetch Access Key
data "azurerm_storage_account" "backup_storage_keys" {
    name                = azurerm_storage_account.backup_storage.name
    resource_group_name = "rg"
}

resource "azurerm_mssql_server_security_alert_policy" "sql_sec_alert_policy" {
    resource_group_name        = "rg"
    server_name                = azurerm_mssql_server.sql.name
    state                      = "Enabled"
    storage_endpoint           = azurerm_storage_account.backup_storage.primary_blob_endpoint
    storage_account_access_key = data.azurerm_storage_account.backup_storage_keys.primary_access_key
    retention_days             = 14
    email_addresses            = [amr@amrfarooq.com]
}

1. Tuning My PITR Settings

• retention_days (1–35):
  I pick this based on how far back I might need to restore. I often use 7 days; for production, you could 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)

My PITR Examples

• Dev/Test (cost-sensitive):
  short_term_retention_policy
{
retention_days = 3
backup_interval_in_hours = 12
}
• Standard:
  short_term_retention_policy
{
retention_days = 7
backup_interval_in_hours = 12
}
• Prod (max window):
  short_term_retention_policy
{
retention_days = 35
backup_interval_in_hours = 12
}

---

2. Defining My LTR Strategy

For long-term needs, you need to describe them using 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

• 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-mydb-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.

---

Putting It All Together

Scenario	PITR Window	LTR Weekly	LTR Monthly	LTR Yearly
Dev/Test	3 days	1 week	—	—
Standard	7 days	1 month	1 year	7 years
Production	35 days	3 months	5 years	10 years