Can you tell me the drive we were backing up server #58 to exactly 8 months ago, and, if possible, it would really help if you could give me the exact file name for me to pull the backup from tape for you.  Or, last night’s backup didn’t finish until after the tapes started, did those backups always take that long and is it taking longer because the databases are actually growing that much?

The answer to those questions tends to be “Ok, no problem.”

SELECT TOP 1000 bs.database_name
	, bs.backup_start_date
	, bs.backup_finish_date
	, backup_duration_minutes = (DateDiff(second, backup_start_date, backup_finish_date)+30)/60
	, backup_duration_seconds = DateDiff(second, backup_start_date, backup_finish_date)
	, backup_size_mb = Cast(bs.backup_size / 1024 / 1024 as Int)
	--, compressed_backup_size_mb = Cast(bs.compressed_backup_size / 1024 / 1024 as Int) --SQL 2008+ only
	, backup_type = CASE bs.type WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' ELSE 'UNKNOWN' END
	, bmf.physical_device_name
	, EntryType = CASE WHEN bs.server_name = @@ServerName THEN 'Backup' ELSE 'Refresh' END
FROM msdb..backupset bs
	INNER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.type <> 'L'
	bs.database_name like  'test_case'
ORDER BY 2 DESC

It’s common in my scripts for me to put 10 things in the where clause and comment them out. My problem is that I don’t want to look around for field names to filter my scripts, but I don’t always want to filter my scripts the same way. It works for me, and you’ll either get used to it or rewrite it for your own use.