List SQL Server Database Files and Backup Sizes

-- ==============================================================================================
-- List SQL Server database files sizes and backup size
-- https://www.mssqltips.com/sqlservertip/6338/sql-server-backup-size-vs-database-size-script/
-- ==============================================================================================

with MostRecentBackups
    as (
        select database_name as [Database],
            max(bus.backup_finish_date) as LastBackupTime,
            case bus.type
                when 'D'
                     then 'Full'
            end as Type
        from msdb.dbo.backupset as bus
        where bus.type <> 'F'
        group by
            bus.database_name,
            bus.type
    ),
BackupsWithSize
    as (
        select mrb.*,
            (
                select top 1 convert(decimal(10, 2), b.compressed_backup_size / 1024 / 1024) as backup_size
                from msdb.dbo.backupset as b
                where [Database] = b.database_name
                    and LastBackupTime = b.backup_finish_date
            ) as [Backup Size],
            (
                select top 1 datediff(s, b.backup_start_date, b.backup_finish_date)
                from msdb.dbo.backupset as b
                where [Database] = b.database_name
                    and LastBackupTime = b.backup_finish_date
            ) as Seconds,
            (
                select top 1 b.media_set_id
                from msdb.dbo.backupset as b
                where [Database] = b.database_name
                    and LastBackupTime = b.backup_finish_date
            ) as media_set_id
        from MostRecentBackups as mrb
    )
select d.name as [Database],
    d.state_desc as State,
    bf.LastBackupTime as LastFull,
    datediff(day, bf.LastBackupTime, getdate()) as TimeSinceLastFullInDays,
    bf.[Backup Size] as FullBackupSizeInMB,
    bf.Seconds as FullBackupSecondsToComplete,
    case
        when datediff(day, bf.LastBackupTime, getdate()) > 14
             then null
        else (
        select top 1 bmf.physical_device_name
        from msdb.dbo.backupmediafamily as bmf
        where bmf.media_set_id = bf.media_set_id
            and bmf.device_type = 2
    )
    end as FullBackupLocalPath,
    (
        select convert(decimal(10, 2), sum(size) * 8.0 / 1024) as size
        from sys.master_files
        where type = 0
            and d.name = db_name(database_id)
    ) as DataFileSize,
    (
        select convert(decimal(10, 2), sum(size) * 8.0 / 1024) as size
        from sys.master_files
        where type = 1
            and d.name = db_name(database_id)
    ) as LogFileSize
from sys.databases as d
left join BackupsWithSize as bf
    on (d.name = bf.[Database] and (bf.Type = 'Full' or bf.Type is null))
--where d.name <> 'tempdb'
where d.name not in ('master', 'model', 'msdb', 'tempdb', 'SSISDB')
    and d.source_database_id is null
order by d.name;