Backup Report

I was working on one the project and they were looking for a backup report which should specify whether it’s physical or logical and few more details. Whatever data I fetched looks good and I felt the same can be helpful for you guys to pull it as a report. If you need any more additions to it please let me know.

Applies To

SQL Server 2005 and Above

SELECT DATENAME([weekday], [bs].[backup_start_date]) [Day Of the Week],
										   CASE [bmf].[device_type]
											  WHEN 2
											  THEN 'Physical'
											  WHEN 5
											  THEN 'Tape'
											  WHEN 7
											  THEN 'Virtual'
											  WHEN 7
											  THEN 'Permanent Backup'
										   END [Virtual\Native],
											  CASE [bs].[type]
												 WHEN 'D'
												 THEN 'Full'
												 WHEN 'I'
												 THEN 'Differential'
												 WHEN 'L'
												 THEN 'Log'
											  END [BackupType],
	  [database_name] [DatabaseName],
	  [bs].[backup_start_date] [StartTime],
	  CONVERT( VARCHAR(10), DATEDIFF([ss], [bs].[backup_start_date], [bs].[backup_finish_date])) + ' seconds' [BackupDuration],
	  [bs].[description] [Description],
					 CASE
						WHEN [bs].[last_family_number] = 1
						THEN 'SplitBackup-BackupSet-' + CONVERT(    VARCHAR(10), [bs].[backup_set_id]) + '-File ' + CONVERT(VARCHAR(4), [bmf].[family_sequence_number])
						ELSE 'Single File'
					 END [SplitBackup],
	  [bmf].[physical_device_name] [BackupDestination],
	  CONVERT( VARCHAR(15), FLOOR([bs].[backup_size] / [bs].[last_family_number] / 1048576)) + ' MB' [BackupSize],
																				  CASE
																					 WHEN [bs].[backup_size] = [bs].[compressed_backup_size]
																					 THEN 'Uncompressed'
																					 ELSE 'Compressed'
																				  END [Compression],
																					 CASE
																						WHEN [bs].[is_snapshot] = 1
																						  OR [bs].[is_copy_only] = 1
																						THEN 1
																						ELSE 0
																					 END [Snapshot\CopyOnly]
FROM [msdb]..[backupset] [bs]
	INNER JOIN [msdb]..[backupmediafamily] [bmf] ON [bmf].[media_set_id] = [bs].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC;