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.