Backup Compression

Question

How to know is the backup file is compressed while taking backup from database.

Ex: I have a sql server 2008 .bak file.
How to know that file in compressed backup file.

Answer 1

Hi,

Run the following command

RESTORE HEADERONLY FROM DISK = 'x:\some_path_here\your_file.bak'

Look at fields BackupSize and CompressedBackupSize

If BackupSize = CompressedBackupSize, it's not compressed else it's compressed.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

Run the RESTORE ... HEADERONLY. That will tell you all you need to know about the .BAK file itself. Then you can also run RESTORE... FILELISTONLY to get the file structures for the restore.    

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

Hi there,

when you take backup SQL server uses the default setting of your server. You should aware of your default setting on your server.

Otherwise you never know are your back ups are compressed or not. However you can over ride the default setting by choosing compressed back up or not compressed.

TO know from the existing backup whether is it compressed or not, use the restore header only command and check for the "compressed" value. 0 - not compressed,1 - compressed

syntax: restore headeronly from disk = N'backup location'

thanks

kumar