I’ve had a number of people ask me how you can tell if the backup compression in SQL Server 2008 really is effective. The easy answer is to try it and look at the results.
To compress a backup, you need to choose the “Compress Backup” option at the bottom of the Options tab in SQL Server Management Studio. Once the backup is done, you can see how effective it was by executing:
RESTORE HEADERONLY FROM DISK = ‘PathToYourBackupHere’
This command has been enhanced to have two columns dealing with backup size. One shows the Backup Size, the other shows the Compressed Backup Size. (Both in bytes).