Resources and Datasources for SQL Server Backups

[alex0ptr]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Description

We would like to use terraform to manage our sql server backups: https://docs.microsoft.com/en-us/rest/api/sql/backupshorttermretentionpolicies/createorupdate

New or Affected Resource(s)

Should be resources and datasources for Backup Policies

Potential Terraform Configuration

References

https://docs.microsoft.com/en-us/rest/api/sql/backupshorttermretentionpolicies/createorupdate

[michaelsew]

In addition to short term backup policy management, I would also add support for sql server Long Term Backup Policy Management while we're at it:

https://docs.microsoft.com/en-us/rest/api/sql/backuplongtermretentionpolicies

The structure of the LTR policy would likely closely resemble the short term policy.

SHORT TERM Backup Policy Parameter:

• properties.retentionDays

LONG TERM Backup Policy Parameters:

• properties.monthlyRetention
• properties.weekOfYear
• properties.weeklyRetention
• properties.yearlyRetention

[bpoland]

If anyone is looking for an interim solution, I found this example ARM template in the comments at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure

You could probably use the terraform ARM template resource to deploy it.

 {
  "type": "Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies",
  "name": "[concat(variables('webDatabaseName'), '/Default')]",
  "tags": { "displayName": "Database Backup" },
  "apiVersion": "2017-03-01-preview",
  "location": "UK West",
  "scale": null,
  "properties": {
    "weeklyRetention": "P4W"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', variables('sqlserverName'), variables('webDatabase'))]",
  ]
},

[dhabierre]

I've found this link (not tested yet):

http://dbainthecloud.com/how-to-deploy-an-azure-sql-database-using-terraform/

[sjentzsch]

We ended up letting Terraform invoke "az rest" with their REST API: https://docs.microsoft.com/en-us/rest/api/sql/backuplongtermretentionpolicies/createorupdate

Worked. Though we would prefer an integration into tf provider and/or azure cli.

[drdamour]

seems like this is part of the go sdk which is usually the roadblock to TF inclusion https://github.com/Azure/azure-sdk-for-go/blob/master/services/sql/mgmt/2014-04-01/sql/backuplongtermretentionpolicies.go

[drdamour]

for those who are interested we ended up using arm templates to define policies like this (slimmed down as much as we could

//must use arm until TF implements these natively see https://github.com/terraform-providers/terraform-provider-azurerm/issues/1802
// backupLongTermRetentionPolicies -> https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/2017-03-01-preview/servers/databases/backuplongtermretentionpolicies
// backupShortTermRetentionPolicies -> https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/2017-10-01-preview/servers/databases/backupshorttermretentionpolicies
//note since we are defining these child resources in an ARM based on parent resources defined
//with TF we have to use their full names see https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/child-resource-name-type#outside-parent-resource
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"
}

this assumes you have a sql server resoucre named example anda db resource in that sql server named example.

[drdamour]

@tombuildsstuff do u know of anything blocking this functionality?

[unagaraju88]

@drdamour Is their any way we can restore the azure deleted databases from LTR backup with tagged resources using terraform arm template?

[lprichar]

I ended up going with @sjentzsch's solution. It's idempotent and avoids downsides of ARM template like importing existing policies. It looks like this:

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 this:

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.

[ghost]

This has been released in version 2.31.0 of the provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading. As an example:

provider "azurerm" {
    version = "~> 2.31.0"
}
# ... other configuration ...

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!