The last report (report 4) must be wrong, backup file names are not recorded in sysaltfiles

-- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

from #Files

where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

and right(FileName,3) = 'bak'

and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

I reckon replace the code with (seems to do the job)

--REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FilePath,[FileName], FileSizeInMB, FileSizeInGB

from #Files [/b]where REPLACE(FilePath + '\' + [FileName], ':\\', ':\')

not in (SELECT

physical_device_name

FROM

master..sysdatabases DB

JOIN

msdb..BACKUPSET BS

ON DB.name = BS.database_name

JOIN msdb..backupmediaset MS

ON

BS.media_set_id = MS.media_set_id

JOIN msdb..backupmediafamily MF

ON

BS.media_set_id = MF.media_set_id

JOIN

(

select max(backup_set_id)as backup_set_id, database_name

FROM msdb.dbo.backupset BS

JOIN msdb.dbo.backupmediaset MS

ON

BS.media_set_id = MS.media_set_id

JOIN msdb.dbo.backupmediafamily MF

ON

BS.media_set_id = MF.media_set_id

and type = 'D'

GROUP BY database_name

) MaxBackup

ON

BS.backup_set_id >= MaxBackup.backup_set_id

and bs.database_name = MaxBackup.database_name)

and right(FileName,3) = 'bak'

and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc [/b]