MS SQL Backup Compression

September 13, 2015

Recently I was wondering how much effect the DB compression would have on saving disk space

Well if you use this script you can follow up and check it.

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'msdb'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

 

image

Result is much better

image