Database Backup compression

Question

I am trying to see all of my backups are compressed or not. I ran below query to check but not getting good result:

select top(10) database_name, 
case when  backup_size = compressed_backup_size then 'Compressed'
else 'Not compressed'
end as Compression, 
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc

I am using:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

By default we setup compression, but once I ran that query I got result database is not compressed.

Answer 1

Your case statement is wrong. Right now if the values are the same then it shows compressed whereas you would want it to show not compressed.

select top(10) database_name, 
case when  backup_size = compressed_backup_size then 'Not compressed'
else 'Compressed'
end as Compression, 
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc