Understanding Azure SQL Long-Term Retention Immutability Configuration with Terraform and AzAPI
Executive Summary Organizations using Azure SQL Database Long-Term Retention (LTR) backup policies may encounter failures when attempting to disable backup immutability while also specifying an immutability mode in the same request. In the investigated scenario, the customer observed that the operation succeeded when performed through ARM templates but failed when executed through Terraform using AzAPI-based resources. The investigation determined that the Azure SQL resource provider enforces validation rules that prevent TimeBasedImmutabilityMode from being specified when TimeBasedImmutability is set to Disabled. The issue was not caused by the Azure SQL service itself, but rather by how configuration values were being submitted through Terraform and AzAPI resource updates. The recommended mitigation is to ensure that immutability mode is omitted or explicitly set to null when disabling time-based immutability. This allows the request to comply with the resource provider's validation requirements. Introduction Azure SQL Database supports immutable Long-Term Retention (LTR) backups to help organizations meet compliance, governance, and data protection requirements. These policies allow administrators to control whether retained backups can be modified or deleted. During an investigation involving Terraform and AzAPI deployments, a customer reported inconsistent behavior when attempting to disable backup immutability. While equivalent ARM template operations completed successfully, Terraform-based deployments generated validation errors. This article explains the observed behavior, the investigation findings, the confirmed root cause, and the recommended mitigation. Issue Description Reported Symptoms The customer reported the following behavior: Enabling and managing Long-Term Retention backup immutability worked successfully. Configurations involving immutability mode settings could be applied successfully under certain conditions. Attempts to disable immutability through Terraform resulted in failures. Similar operations appeared to succeed when performed using ARM templates. Technical Environment The discussion confirmed the following components: Azure SQL Database Long-Term Retention (LTR) backup policies Backup immutability configuration Terraform deployments AzAPI resources and AzAPI resource updates ARM template deployments Azure SQL Resource Provider validation logic Expected Behavior When administrators disable backup immutability, the configuration update should be accepted and the policy should transition to a disabled state. Actual Behavior Requests submitted through Terraform/AzAPI included both: TimeBasedImmutability = Disabled TimeBasedImmutabilityMode = Unlocked The Azure SQL resource provider rejected this configuration, returning an error indicating that an immutability policy mode cannot be specified when backup immutability is not enabled. Investigation and Troubleshooting 1. Initial Customer Question The customer sought clarification on whether enabling, disabling, locking, and unlocking backup immutability should all be possible through AzAPI resources and whether a product issue existed. 2. Review of Azure SQL Resource Provider Behavior The support team reviewed requests submitted to the Azure SQL resource provider and compared successful and unsuccessful operations. The investigation focused on configuration differences between ARM template deployments and Terraform-driven updates. Confirmed Finding When ARM templates disabled immutability, the request contained: TimeBasedImmutability = Disabled and did not include an immutability mode parameter. Confirmed Finding When Terraform attempted to disable immutability, the request included both: TimeBasedImmutability = Disabled TimeBasedImmutabilityMode = Unlocked This resulted in a validation failure from the Azure SQL resource provider. 3. Validation of Error Behavior The team verified that the error was generated by the Azure SQL resource provider and was reproducible outside Terraform, including equivalent testing through ARM deployments when the conflicting parameter combination was supplied. Confirmed Error The resource provider returned an error equivalent to: Cannot set immutability policy mode when backup immutability is not enabled. 4. Assessment of Terraform and AzAPI Behavior The investigation identified an important behavioral difference. Terraform itself did not yet expose dedicated Time-Based Immutability parameters in its SQL modules. As a result, the customer was using AzAPI resources to perform direct REST-based operations. The team discovered that: azapi_resource behaved as expected. azapi_resource_update could retrieve and reuse an existing property value when no value was explicitly provided. This behavior caused the immutability mode value to persist unexpectedly during updates. 5. Reproduction and Verification The engineering discussion included review and validation of the reported behavior. Testing confirmed that requests containing immutability mode while immutability was disabled were expected to fail due to platform validation. Root Cause Confirmed Root Cause The failure occurred because the update request attempted to disable backup immutability while simultaneously providing a value for TimeBasedImmutabilityMode. Azure SQL validation rules require immutability mode to be associated only with an enabled immutability configuration. When immutability is disabled, an immutability mode must not be supplied. An additional contributing factor was the behavior of azapi_resource_update, which could retain a previously configured immutability mode value when no new value was explicitly provided. Consequently, requests unintentionally included an immutability mode even though the intent was to disable immutability entirely. The available evidence supports this conclusion through: Comparison of successful ARM template requests and failing Terraform requests. Reproduction of the same validation behavior by the Azure SQL resource provider. Validation of the AzAPI update behavior involving retained values. Mitigation and Resolution Recommended Mitigation When disabling backup immutability: Set TimeBasedImmutability to Disabled. Do not provide TimeBasedImmutabilityMode. Terraform/AzAPI Workaround The investigation determined that explicitly setting: TimeBasedImmutabilityMode = null prevents the previous value from being reused and allows the request to be processed correctly. Configuration Matrix Discussed The support team identified the following expected behavior: Operation Immutability Mode Requirement Locking backups Mode should be set to Locked Unlocking while remaining enabled Mode may be supplied and is recommended for clarity Disabling immutability Mode should not be supplied This guidance was explicitly discussed during the investigation. Validation After applying the mitigation: The disable operation should complete without the immutability mode conflict. Requests should no longer trigger the Azure SQL validation error related to immutability mode usage. Recommendations and Best Practices Recommendations Supported by the Investigation Ensure that immutability mode is not included when disabling backup immutability. Review Terraform templates for dynamically generated properties that may continue to emit previously populated values. When using AzAPI update resources, explicitly manage nullable properties where supported to avoid unintended value persistence. Important Considerations Behavior may vary depending on: Azure SQL API version Terraform provider version AzAPI provider implementation details Existing Long-Term Retention backup state Whether previously locked backups exist Always validate deployment behavior in a non-production environment before applying configuration changes broadly. Conclusion This investigation demonstrated that the inability to disable Azure SQL Long-Term Retention backup immutability was not caused by a platform defect in Azure SQL. Instead, the failure occurred because requests attempted to specify an immutability mode while immutability itself was being disabled. The issue was further influenced by AzAPI update behavior that could preserve previously configured values unless explicitly cleared. Setting the immutability mode to null, or removing it entirely when disabling immutability, resolved the problem. The key technical takeaway is that TimeBasedImmutabilityMode and TimeBasedImmutability must be configured consistently with Azure SQL resource provider validation rules, particularly during infrastructure-as-code deployments. Public Documentation Azure SQL Database Long-Term Retention documentation Azure SQL Backup Immutability documentation ARM/Bicep resource documentation for backup Long-Term Retention policies Terraform provider documentation for Azure SQL DatabaseLessons Learned #563: How to Check Which Backups Are Available in Azure SQL Database
In several support cases I’ve worked on, DBAs wanted to see the history of automated backups for their Azure SQL databases. I start by checking two things the DBAs can verify: the database’s service tier and its backup retention settings. These determine which tools to use and what each one can show. Start with your backup retention settings Your configuration What you can see Where to look Short-term retention for PITR The configured retention period and the earliest date and time available for restore. On supported non-Hyperscale tiers, you can also see the types and completion times of individual backup records still visible to the database. Restore in the Azure portal or az sql db show for the earliest restore date. Use az sql db str-policy show for the policy and sys.dm_database_backups for individual backup records. Long-term retention (LTR) The weekly, monthly, or yearly policy in effect today and the full backups still retained, including their timestamps and expiration dates. Backups → Available LTR backups → Manage on the logical server, or az sql db ltr-policy show and az sql db ltr-backup list. Hyperscale The PITR restore window and any retained LTR copies. Hyperscale uses snapshots, so sys.dm_database_backups returns no rows. Use the Azure portal or Azure CLI for the restore window and LTR inventory. For example, with seven days of short-term retention and one monthly LTR backup retained for 12 months, I would use PITR to check whether I can restore to a specific time yesterday. For a date six months ago, I would list the LTR backups and check which backup timestamp is available. If I needed to see when a recent backup completed, I would query the DMV on a supported tier. Inspect completed backup metadata with SQL For supported non-Hyperscale Azure SQL databases, connect to the database being reviewed and run this read-only query: SELECT backup_start_date, backup_finish_date, backup_type, in_retention FROM sys.dm_database_backups ORDER BY backup_finish_date DESC; Interpret backup_type as D for full, I for differential/incremental, and L for transaction log. The timestamps describe the backup operation, not the time an audit report was collected. Its history depends on retention. For Hyperscale, this DMV returns no rows because its backup architecture is snapshot-based; an empty result there is not proof that backups failed. Reference: sys.dm_database_backups. Why I could see week-old backups with one day of PITR retention I checked an Azure SQL database configured with one day of point-in-time restore (PITR) retention. These were the relevant timestamps. What I checked Date and time (UTC) Full backup visible in the DMV (D, in_retention = 0) November 7, 2026, 10:19:19 (start) First transaction log backup visible in the DMV (L, in_retention = 0) November 7, 2026, 10:19:22 (start) First differential backup visible in the DMV (I, in_retention = 0) November 10, 2026, 12:46:52 (finish) Earliest restore time reported by Azure (earliestRestoreDate) November 13, 2026, 09:28:13 First backup visible in the DMV with in_retention = 1 (L) November 13, 2026, 23:43:06 (finish) Second differential backup visible in the DMV (I, in_retention = 1) November 13, 2026, 23:43:56 (finish) The first visible log backup started three seconds after the full backup started, while the full was still running. For this backup model, Azure takes full backups weekly and continues taking transaction log backups between differentials. The differential interval can be 12 or 24 hours. This DMV capture shows two differentials, on November 10 and 13. To check the current setting, look for diffBackupIntervalInHours in the short-term retention policy. Automated backups in Azure SQL Database The one-day setting governs the PITR window. To support recovery within that window, Azure can retain a full backup from before the window opened, along with the differential and log backups it needs. With weekly full backups, this can mean keeping backups for up to a week beyond the configured retention period. The DMV marks backups within the configured retention period as in_retention = 1 and can also show older backups marked in_retention = 0. Backup storage consumption in Azure SQL Database A restore to November 13 at 12:45 UTC illustrates the difference. That time fell within the window Azure reported, even though the first visible row marked in_retention = 1 was more than ten hours later. For PITR, Azure selects a full backup, an applicable differential if needed, and the transaction log backups that cover the requested time. A differential already includes the changes since its base full backup. In this example, the full from November 7 and the differential from November 10 illustrate how the chain could work. The differential from November 13 at 23:43 UTC was taken after the requested time. The DMV rows alone leave the actual restore sequence unverified. I used earliestRestoreDate to verify the start of the reported window. This example describes a restore I could request at the time of the check. I did not run it. The customer's follow-up on long-term retention After we discussed the limits of the backup history, DBAs commonly ask three questions about long-term retention (LTR): how it works, whether they can retain backups for less than a year and what it costs, and whether enabling it affects production performance. How does LTR work? Azure copies selected automated full backups to long-term storage according to a policy set per database. You can retain weekly, monthly, and yearly copies for up to 10 years. For yearly retention, you also choose the week of the year. Each copy can be restored as a new database at the timestamp of that backup. When you first enable LTR, Azure copies the latest eligible full backup, and it can take up to seven days for the first copy to appear. Expired backups cannot be recovered by enabling the policy today. Can we keep backups for less than a year, and what does it cost? Yes. For example, you can retain one full backup per month for three months. LTR storage is billed separately based on consumption in GB per month. The cost depends on the retained backup volume, the region, and storage redundancy. Changes to the policy apply to future backups. Existing copies keep their original expiration dates. Azure SQL Database pricing · Azure Pricing Calculator Will enabling it affect production? Azure copies backups it already creates in a background operation. Microsoft documents that the LTR copy has no performance impact on the database workload. Long-term retention overview What the backup inventory can show for a past reporting period For example, if the audit period runs from July 1, 2025 through June 30, 2026, and the inventory shows 12 retained monthly LTR copies, I would report 12 retained recovery points with their timestamps and expiration dates. The outcome of individual backup runs between those points would remain unverified. I would also record the actual fiscal-year boundaries in UTC, using an inclusive start and an exclusive end. Review PITR and LTR in the Azure portal PITR supports recovery to a chosen time inside the available short-term window. LTR preserves selected automated full backups and supports recovery to the timestamp of a retained backup. LTR does not extend arbitrary point-in-time recovery across the entire long-term period. For a read-only review, open the logical SQL server in the portal and select Backups. Under Retention policies, inspect the selected database's short-term and long-term settings. To inspect its retained LTR inventory, select Manage in Available LTR backups. For PITR, open the database resource and select Restore to view the earliest available restore point. Leave the form without submitting it. To configure LTR, open Backups → Retention policies on the logical server, select the database, and choose Configure policies. For one full backup per month retained for 12 months, set Weekly = 0, Monthly = 12 months, and Yearly = 0, then select Apply. Check which databases are selected before applying the policy. Reference: Manage long-term backup retention. Collect backup evidence with Azure CLI Run these read-only examples in Bash or Azure Cloud Shell. Use the logical server's short name and the Azure region of the backups. Passing the subscription explicitly keeps each command scoped to the intended subscription. subscription_id="<subscription-id>" resource_group="<resource-group>" sql_server="<logical-sql-server>" sql_database="<database-name>" backup_region="North Europe" Check current PITR availability and retention az sql db show \ --subscription "$subscription_id" \ --resource-group "$resource_group" \ --server "$sql_server" \ --name "$sql_database" \ --query '{database:name,earliestRestoreDate:earliestRestoreDate}' \ --output json az sql db str-policy show \ --subscription "$subscription_id" \ --resource-group "$resource_group" \ --server "$sql_server" \ --name "$sql_database" \ --output json Example output from the first command (synthetic): { "database": "audit-example-db", "earliestRestoreDate": "2026-10-05T12:34:56Z" } earliestRestoreDate is the earliest PITR time the service reports at collection time. The short-term policy reports retentionDays and diffBackupIntervalInHours, which describe the current PITR retention period and differential backup interval. These values describe current restore availability and configuration. A completed test restore provides separate evidence of recovery. Read the current LTR policy az sql db ltr-policy show \ --subscription "$subscription_id" \ --resource-group "$resource_group" \ --server "$sql_server" \ --name "$sql_database" \ --query '{weeklyRetention:weeklyRetention,monthlyRetention:monthlyRetention,yearlyRetention:yearlyRetention,weekOfYear:weekOfYear}' \ --output json Example output (synthetic): { "weeklyRetention": "P12W", "monthlyRetention": "P12M", "yearlyRetention": "P5Y", "weekOfYear": 1 } This policy selects weekly backups for 12 weeks, monthly backups for 12 months, and a yearly backup from week 1 for five years. The command shows the current policy. Its output alone cannot establish which settings were in effect during a previous fiscal year. List retained LTR backups az sql db ltr-backup list \ --subscription "$subscription_id" \ --location "$backup_region" \ --resource-group "$resource_group" \ --server "$sql_server" \ --database "$sql_database" \ --query '[].{BackupTime:backupTime,Expires:backupExpirationTime,BackupName:name}' \ --output table Use the backup timestamp to identify entries within the fiscal-year boundaries. For an evidence export, use --output json and omit --query to preserve the full metadata, including backup identifiers. Omit --only-latest-per-database to keep older retained entries in the results. If the command succeeds and returns no entries for the audit period, record that result with the subscription, region, server, database, and collection timestamp. Verify the scope and consider whether the database was moved or recreated under the same name. The current inventory alone cannot establish whether a policy was absent during the period or whether retained copies later expired or were deleted. References Automated backups in Azure SQL Database Long-term retention overview Manage long-term retention Get-AzSqlDatabase Get-AzSqlDatabaseBackupShortTermRetentionPolicy Get-AzSqlDatabaseBackupLongTermRetentionPolicy Get-AzSqlDatabaseLongTermRetentionBackup Azure CLI database properties and PITR Azure CLI short-term retention policy Azure CLI long-term retention policy Azure CLI long-term backup inventory Azure SQL Database restore-points API View Azure SQL Database audit logs
