How to list last backup date of all databases in MSSQL

To list last backup date of all databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT db.name AS [Databasename], 
case when	MAX(bck.backup_finish_date) is NULL then 
				'No Backup' 
			else 
				convert(varchar(100), MAX(bck.backup_finish_date)) 
end AS [DateOfLastBackup]
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset bck 
	ON db.name = bck.database_name AND bck.type = 'D' WHERE db.database_id NOT IN (2) 
GROUP BY db.name
ORDER BY 2 ASC

Please Share us:
Please rate this snippet

One thought on “How to list last backup date of all databases in MSSQL

Leave a Reply