Lessons 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
