locked
Backup Compression RRS feed

  • 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.
    Tuesday, January 8, 2013 6:04 PM

Answers

  • 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/

    • Marked as answer by Evne Maximoy Sunday, January 13, 2013 6:11 PM
    Tuesday, January 8, 2013 6:38 PM

All replies

  • 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

    Tuesday, January 8, 2013 6:14 PM
  • 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/

    • Marked as answer by Evne Maximoy Sunday, January 13, 2013 6:11 PM
    Tuesday, January 8, 2013 6:38 PM
  • 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

    Wednesday, January 9, 2013 5:12 AM