GetSQLFileSizesAndBackupTimes.sql

WITH fs
AS
(
    SELECT database_id, name, type, size * 8.0 / 1024 AS size
    FROM sys.master_files
)
SELECT 
    db.name AS Name,
    db.database_id AS ID,
    CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 0 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS FileSizeMB,
    CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 1 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS LogSizeMB,
    (SELECT MAX(bus.backup_finish_date) FROM msdb.dbo.backupset AS bus JOIN fs ON bus.database_name = fs.name) AS LastBackup
FROM sys.databases AS db