Azure SQL Database backup history

May 4, 2022

There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.

If you run the example query as-is from the above documentation some of the columns do not make sense.

SELECT * 
FROM sys.dm_database_backups     
ORDER BY backup_finish_date DESC;

Instead, use this query where I am joining with sys.databases and using the CASE statement. Run this in the master database to get a backup history for all databases in an Azure SQL Server.

SELECT 
  db.name,
  backup_start_date, 
  backup_finish_date,
  CASE backup_type
	WHEN 'D' THEN 'Full'
	WHEN 'I' THEN 'Differential'
	WHEN 'L' THEN 'Transaction Log'
	END AS BackupType, 
  CASE in_retention
	WHEN 1 THEN 'In Retention'
	WHEN 0 THEN 'Out of Retention'
	END AS is_Bakcup_Available
FROM sys.dm_database_backups AS ddb
JOIN sys.databases AS db
ON ddb.physical_database_name = db.physical_database_name
/*
--excluding master database
WHERE db.name <> 'master'
*/
ORDER BY backup_start_date DESC

Run the below query in the user database context to get a backup history for a single database.

SELECT
  backup_start_date, 
  backup_finish_date,
  CASE backup_type
  WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS BackupType, 
  CASE in_retention
  WHEN 1 THEN 'In Retention'
  WHEN 0 THEN 'Out of Retention'
  END AS is_Bakcup_Available
FROM sys.dm_database_backups AS ddb
JOIN sys.databases AS db
ON ddb.physical_database_name = db.physical_database_name
ORDER BY backup_start_date DESC

5 replies on “Azure SQL Database backup history”

Thank you very much. its really helpful to complete my task in short time.

The sample query is incorrect, the join on physical_database_name is incorrect, should be using “ddb.logical_database_name = db.name”

That will not work. What error do you get when you run this as is? Sys.dm_database_backups does not have a column you can join with the name column of sys.databases.

Can we get a mail when ever the backup is completed or if it failed in azure SQL database

Yes, you can by setting up an elastic job whenever a new entry is recorded about the backup. Microsoft guarantees the SLA, so I wonder why you would want an email for every successful backup!