A while back I wrote a post on SQL 2008′s Backup Compression where I took a sample database and performed a backup with and without compression being used and did some basic comparisons.
So let’s say now you’ve been using SQL 2008 with the backup compression for a while now and would like to see on average how much of a savings this has given you per database. This query takes the average compressed backup size and divides it by the average uncompressed (raw) backup size and shows the difference in percentage between the compressed and uncompressed.
The query is setup to only look at full backups (type = ‘D’)
Something else you’ll notice in the WHERE clause is that I’m only looking at rows in the backupset table where compressed_backup_size does not equal the backup_size. The reason I do this is that when I was testing the query I noticed that entries where I purposely did not make use of the backup compression functionality that these values were the same which in turn affected my results. What I’m interested in obtaining is a comparison when the backup compression WAS used. (If there is an easier way to determine if compression was used please comment and I’ll change my query accordingly)
SELECT database_name,
AVG(ROUND(backup_size / 1048576,2)) AS 'AvgRawSize_MB',
AVG(ROUND(compressed_backup_size / 1048576,2)) AS 'AvgCompressedSize_MB',
CONVERT(INT,100*(AVG(ROUND(compressed_backup_size / 1048576,2)))/
AVG(ROUND(backup_size / 1048576,2))) AS 'PCT_OfRawSize'
FROM msdb.dbo.backupset WITH(NOLOCK)
WHERE TYPE = 'D' AND
compressed_backup_size <> backup_size
GROUP BY database_name
ORDER BY database_name
Enjoy!!
