Get SQL Server Backup History With Compression Ratio

There are many occasions where backup compression is not enabled on the SQL Server by default. There are a lot of third-party tools like IBM Tivoli, Netbackup, Netvault which we can use to backup databases.  It is always handy to have a script to check which are compressed using the SQL compression which was introduced from SQL Server 2005 and later versions.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
bs.database_name, 
bs.backup_start_date, 
bs.backup_finish_date, 
bs.expiration_date, 
CASE bs.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
bs.backup_size, 
bmf.logical_device_name, 
bmf.physical_device_name, 
bs.name AS backupset_name, 
bs.description,
bs.compressed_backup_size/1024/1024 AS 'Compressed_Backup_Size_MB',
bs.backup_size/1024/1024 AS 'Backup_Size_MB',
CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bs.backup_start_date, bs.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time],
100-(CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, bs.compressed_backup_size) /CONVERT (FLOAT, bs.backup_size))*100)) 'Compression Ratio'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 1)   
ORDER BY 
bs.database_name