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.
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;