Get Backup History for All Databases in Server

Here’s a quick snippet to get a listing of the database backups that last occurred on a server. Most solutions provided a single backup listing, but not the brief summary of the last backup details I was looking for.

Get last backup history on all databases on a sql server by evaluating the backupset table in msdb

GitHub

select
	sdb.name														as DatabaseName
	,coalesce(convert(varchar(12), x.backup_finish_date, 101), '-')	as LastBackUpTime
	,x.*
from
	sys.sysdatabases as sdb

	cross apply (
		select top (1)
			bus.type
			,bus.backup_finish_date
			,type_description = case bus.type
									when 'D' then 'Database'
									when 'I' then 'Differential database'
									when 'L' then 'Log'
									when 'F' then 'File or filegroup'
									when 'G' then 'Differential file'
									when 'P' then 'Partial'
									when 'Q' then 'Differential partial'
									else bus.type
								end
			,bus.user_name
			,bus.recovery_model
			,bus.is_copy_only


		from
			msdb.dbo.backupset as bus
		where
			bus.database_name = sdb.name
		order by
			bus.backup_finish_date desc) as x

order by
	LastBackUpTime desc