1

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.

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.