View SQL Server Backup History

For the occasions that you need to check the backup history of your SQL Instance this script is helpful.

/******************
* Title:	View Latest Backup History
* Notes:	Provides detail on the last backups and when they took place.
*******************/
SELECT
	 bs.[database_name]												AS DatabaseName
	,bs.backup_start_date											AS DateStart
	,bs.backup_finish_date											AS DateFinished
	,DATEDIFF(MI, bs.backup_start_date, bs.backup_finish_date)		AS DurationMin
	,DATEDIFF(SS, bs.backup_start_date, bs.backup_finish_date)		AS DurationSec
	,CASE bs.[type]
		WHEN 'D' THEN 'Full Backup' 
		WHEN 'I' THEN 'Differential Database' 
		WHEN 'L' THEN 'Log' 
		WHEN 'F' THEN 'File/Filegroup' 
		WHEN 'G' THEN 'Differential File'
		WHEN 'P' THEN 'Partial'  
		WHEN 'Q' THEN 'Differential partial' 
	 END															AS BackupType
	,CAST(ROUND(((bs.backup_size/1024)/1024),2) AS decimal(18,2))	AS BackupSizeMb
	,CAST(
		ROUND(((bs.compressed_backup_size/1024)/1024),2) 
												AS decimal(18,2))	AS CompressedBackupSizeMb
	,'' 
	+ ISNULL(
	  CASE WHEN has_bulk_logged_data = 1 THEN '| Bulk Logged Data' END
	+ CASE WHEN is_snapshot = 1	THEN '| Snapshot' END
	+ CASE WHEN is_readonly = 1 THEN '| Read-Only' END
	+ CASE WHEN is_single_user = 1 THEN '| Single User' END
	+ CASE WHEN has_backup_checksums = 1 THEN '| Backup Checksums' END
	+ CASE WHEN is_damaged = 1 THEN '| Damaged' END
	+ CASE WHEN begins_log_chain = 1 THEN '| Begins Log Chain' END
	+ CASE WHEN begins_log_chain = 1 THEN '| Incomplete Metadata' END
	+ CASE WHEN is_force_offline = 1 THEN '| Force Offline' END
	+ CASE WHEN is_force_offline = 1 THEN '| Copy Only' END	
	,'')															AS BackupOptions	
	,bmf.physical_device_name										AS BackupFile
	,bs.[user_name]													AS ExecUser
	,CASE 
		WHEN bs.database_name IN ('model','master','tempdb','msdb')
		THEN 1
		ELSE 0
	 END															AS IsSystemDatabase
FROM msdb.dbo.backupmediafamily AS bmf
	INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id;
GO