SQL Server – Get Latest Backup File Sizes For All Databases

This returns a result for each database and includes its size in MB.

SELECT DATABASE_NAME DBNAME
,(COMPRESSED_BACKUP_SIZE / (1024*1024) ) [COMPRESSED BACKUP IN MB]
FROM MSDB..BACKUPSET BUS
WHERE BUS.BACKUP_SET_ID IN
      ( -- GET THE NEWEST BACKUP
            SELECT MAX(BACKUP_SET_ID)ASPState
            FROM MSDB..BACKUPSET
            WHERE TYPE = 'D' -- FULL BACKUP ONLY
            GROUP BY DATABASE_NAME
      )
ORDER BY DBNAME