SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
convert (decimal(30,2), (msdb.dbo.backupset.backup_size + 1536) / 1024) As SizeInKB,
convert (decimal(30,2), (msdb.dbo.backupset.backup_size + 1536) / 1024 / 1024) As SizeInMBs,
convert (decimal(7,3), (msdb.dbo.backupset.backup_size + 1536) /1024 / 1024 / 1024) As SizeInGBs
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
--pull only last 90 days
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 90)
--only want logs
AND msdb..backupset.type = 'L'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date