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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.