How to identify weather a Database Backup Set was compressed using WITH COMPRESSION

  • Article

This is in continuation with my first blog “SQL Server 2008 Backup Compression”. I received this query from one of the reader about “How to determine, if a Database Backup set was compressed using WITH COMPRESSION”. A simple way to identify this to use RESTORE HEADERONLY.

RESTORE HEADERONLY command can be used to fetch all information about a backup residing on a backup device. The command output includes a column called ‘Compressed’ with values as 0 or 1. Whether the backup set is compressed using software-based compression:

  • 0 = No
  • 1 = Yes

 

Let’s verify this with an example:

Please note, we are reusing our existing backup sets ‘Before_Compression.bak’ and ‘With_Compression.bak’.

-----------------------------------------------------

RESTORE HEADERONLY

FROM DISK = 'D:\tempdb\Before_Compression.bak'

clip_image001

-----------------------------------------------------

and now,

-----------------------------------------------------

RESTORE HEADERONLY

FROM DISK = 'D:\tempdb\With_Compression.bak'

clip_image002

-----------------------------------------------------

As always, for more details of RESTORE HEADERONLY, I request you to please refer SQL Server 2008 BOL >>

https://technet.microsoft.com/en-us/library/ms178536.aspx

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.