Azure SQL back up setting in Terraform

Question

Trying to set a parameter in Terraform file to back-up an Azure SQL server.

In AWS i can add a key like:

      allocated_storage = 1000
      engine_version = “13.00.4451.0.v1”
      backup_window = “18:00-18:30”
      backup_retention_period = 14

I can't find a similar key to back-up an Azure SQL. Below is the back up key that i have found in Terraform Docs and on the web:

  resource "azurerm_sql_server" "some_erver" {
    administrator_login          = "some_login"
    administrator_login_password = "some_password"
    location                     ="some_location"
    name                         = "some_server"
    resource_group_name          = "some_resource"
    version                      = "some_version"

  tags = {
    environment  = "t"
    stack-name   = "name"
    stack-number = "number"
    app-number   = "app-number"
    logicmonitor = "bool"
  }
  backup {
    frequency = "Daily"
    time      = "04:00"
  }

  retention_daily {
    count = 30
  }
}

But the Terraform Apply pipeline fails with a message:

     Error: azurerm_sql_server.enxtz256-db01: : invalid or unknown key: backup

     Error: azurerm_sql_server.enxtz256-db01: : invalid or unknown key: retention_daily

Any help would be much appreciated

I know it is a bit late, but I suggest you migrate to azurerm_mssql_server, which actually supports the same database types and includes short- and long-term backup. — Cloudkollektiv, Dec 29 '20 at 12:24

Cloudkollektiv · Accepted Answer · 2022-04-06T16:12:09.000

I would like to point out that according to the development team (source) azurerm_sql_database is not getting updated anymore. Instead, all new features are added to azurerm_mssql_database, which actually deploys the same database types. According to the Terraform documentation, azurerm_mssql_database supports both short-term backups (point-in-time recovery) as well as long-term backups. This can be managed by the following in-line blocks:

A long_term_retention_policy block supports the following:

weekly_retention - (Optional) The weekly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 520 weeks. e.g. P1Y, P1M, P1W or P7D.

monthly_retention - (Optional) The monthly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 120 months. e.g. P1Y, P1M, P4W or P30D.

yearly_retention - (Optional) The yearly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 10 years. e.g. P1Y, P12M, P52W or P365D.

week_of_year - (Optional) The week of year to take the yearly backup in an ISO 8601 format. Value has to be between 1 and 52.

A short_term_retention_policy block supports the following:

retention_days - (Required) Point In Time Restore configuration. Value has to be between 7 and 35.

If you somehow came from azurerm_sql_database and want to migrate to azurerm_mssql_database you could follow the steps below. I have done this myself, and it is quite easy.

Run: terraform state rm "azurerm_sql_database.database"
Change the resource to azurerm_mssql_database, including the appropriate fields
Run: terraform import azurerm_mssql_database.database "/very/long/database/id"
Run: terraform apply

https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_database

.

score 3 · Answer 2 · answered Nov 26 '19 at 07:12

From the terraform document azurerm_sql_server, it doesn't has the backup and retention_daily block support.

If you want to create the backup of Azure SQL database, maybe you can think about using azurerm_recovery_services_protection_policy_vm.

Example:

resource "azurerm_resource_group" "example" {
  name     = "tfex-recovery_vault"
  location = "West US"
}

resource "azurerm_recovery_services_vault" "example" {
  name                = "tfex-recovery-vault"
  location            = "${azurerm_resource_group.example.location}"
  resource_group_name = "${azurerm_resource_group.example.name}"
  sku                 = "Standard"
}

resource "azurerm_recovery_services_protection_policy_vm" "test" {
  name                = "tfex-recovery-vault-policy"
  resource_group_name = "${azurerm_resource_group.example.name}"
  recovery_vault_name = "${azurerm_recovery_services_vault.example.name}"

  timezone = "UTC"

  backup {
    frequency = "Daily"
    time      = "23:00"
  }

  retention_daily {
    count = 10
  }

Reference blog: Terraform Azurerm Recovery Services Vault Backup Policy Format Error

I also find another tutorial can help create the backup for Azure SQL database with terraform: How To Deploy an Azure SQL Database using Terraform.

Summary:

Azure SQL Database

Now, to the SQL Database. You can see here, the resource blocks are pretty much the same, give it a name, resource group and location.

Notice, at the end of this block, I added a PowerShell command to set the long term backup retention policy for the database. This will give you the ability to set monthly or weekly backup retention plans to fit your needs.

resource "azurerm_sql_database" "test2" {
  name                = "sqldbtf01"
  resource_group_name = "${azurerm_resource_group.test2.name}"
  location            = "North Central US"
  server_name         = "${azurerm_sql_server.test2.name}"

  threat_detection_policy {
    state                      = "Enabled"
    email_addresses            = ["dbgrl93@gmail.com"]
    retention_days             = "30"
    storage_account_access_key = "${azurerm_storage_account.test2sa.primary_access_key}"
    storage_endpoint           = "${azurerm_storage_account.test2sa.primary_blob_endpoint}"
    use_server_default         = "Enabled"
  }

  provisioner "local-exec" {
    command     = "Set-AzureRmSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName ${azurerm_resource_group.test2.name}  -ServerName ${azurerm_sql_server.test2.name} -DatabaseName 'sqldbsrvrtf01' -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16 "
    interpreter = ["PowerShell", "-Command"]
  }
}

Hope this helps.

i will try this now and let you know in some hours. Thanks for sharing. — Marvelous, Nov 26 '19 at 07:27
Thanks. i've created at least a long term backup policy using the above code. — Marvelous, Nov 26 '19 at 11:54

Lee Richardson · Answer 3 · 2020-08-01T17:27:34.697

You can try an ARM template:

resource "azurerm_template_deployment" "example" {
  name                = "example-db-retention"
  resource_group_name = azurerm_resource_group.example.name
  template_body = <<-DEPLOY
    {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "resources": [
        {
          "apiVersion" : "2017-03-01-preview",
          "type" : "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
          "name" : "${azurerm_sql_server.example.name}/${azurerm_sql_database.example.name}/default",
          "properties" : {
            "weeklyRetention": "P45D"
          }
        },
        {
          "apiVersion": "2017-10-01-preview",
          "type": "Microsoft.Sql/servers/databases/backupShortTermRetentionPolicies",
          "name": "${azurerm_sql_server.example.name}/${azurerm_sql_database.example.name}/default",
          "properties": {
            "retentionDays": 14
          }
        }
      ]
    }
    DEPLOY

  deployment_mode = "Incremental"
}

which I got from drdamour in Issue 1802 in the azurerm terraform provider github site. This ultimately doesn't work very well for my scenario since it doesn't track the object in Terraform, but it might work for you.

Alternately I ended up just calling it from azure cli with azure rest command like:

az rest --method put --uri https://management.azure.com/subscriptions/[subscription]/resourceGroups/[resourcegroup]/providers/Microsoft.Sql/servers/[servername]/databases/[databasename]/backupLongTermRetentionPolicies/default?api-version=2017-03-01-preview --body "{\"properties\":{\"weeklyRetention\":\"P4W\",\"yearlyRetention\":\"P7Y\",\"weekOfYear\":1}}"

to set long term retention, and then:

az rest --method put --uri https://management.azure.com/subscriptions/[subscription]/resourceGroups/[resourcegroup]/providers/Microsoft.Sql/servers/[servername]/databases/[databasename]/backupShortTermRetentionPolicies/default?api-version=2017-10-01-preview --body "{\"properties\":{\"retentionDays\":14}}"

to set short term retention. I like this solution because it's simple and idempotent.

More details on API at Short Term Retention Policies and Long Term Retention Policies.

Azure SQL back up setting in Terraform

3 Answers3