SQL SERVER – 2008 – Introduction to New Feature of Backup Compression

Backup and Data Storage is my most favorite subject and I have not written about this for some time. I was experimenting with new feature of SQL Server 2008 and I come across very interesting feature of Backup compression.

Let us see example of Database AdventureWorks with and without compression. After taking backup with compression enabled and without compression the file size can be compared to see the difference it makes with compressing the database.
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AW_NoCompression.bak'
GO
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AW_WithCompression.bak'
WITH COMPRESSION
GO

SQL Server 2008 supports backup data compression at database level. First of all enable advance option and set the server level setting for backup compression on. As backup compression settings are turned on all the future backup will be taken with backup compressions. There is no need to write any other extra clauses described in script above.
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced option', '0';
RECONFIGURE
GO

This is interesting feature should be use with care. Compression is based on what kind of the data is being compressed. If you are storing images in database it will be difficult to compress that kind of data. Taking compressed backup is better for input/output operations as there is less amount of the data has to be written on the database. Compressed backup is resource intense process, therefore it should be used with care. If database is busy all the time this feature should be turned off.

Yes, I plan to use this feature on my own production server when I upgrade my current servers to latest version.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

19 thoughts on “SQL SERVER – 2008 – Introduction to New Feature of Backup Compression

  1. Hi,
    Could you make more tests with compression backups, I would like to know how much time does it take to backup a larger database (lets say 20 GB) with and without compression.

    Also, it would be interesting to know how much CPU and memory is used, do you know where is the data being stored while the server is compressing the file (memory or disk)?

    Thanks.

  2. Dear Sir,
    I have seen ur blog. It is realy fantastic. One thing i want to know, i m working in a software company as a Customer support Executive by 2 years. Now i want to improve my carier in Sql DBA. So can please give me some suggestion for this.

    Thanks
    Mudit

  3. Pingback: SQL SERVER - 2008 - Introduction to Row Compression Journey to SQL Authority with Pinal Dave

  4. Hi Dave,

    This is very interesting feature. But currently i instaled SQL 2008 Express with Tool, in the server option i see the Compress Backup checkbox, then i checked in. But it does not affect.

    Then i tried your SQL statement to enable this by command. But it does not works too.

    Did this feature avaiable in the express version?

  5. Hello Zafar,

    The ratio of backup depends of the data of database. Few factors like data type of data, consistency of data, whether data is already compressed or encripted decides the compression ratio. But normally comprassion is 40% to 80% of uncompressed backup size.
    You can check the ratio in the “Backup_size” and “Compressed_backup_size” columns of backupset table.
    Backup as well as resotre are faster on comprassed backup. The only thing that increases is CPU utilization.

    Regards,
    Pinal Dave

  6. Dear Sir,

    Is the compress backup functionaly only support Microsoft SQL Server 2008 Enterprise edition?

    I tried to executed on my productin SQL Server 2008 Standard edition:

    EXEC sp_configure ‘backup compression default’, ‘1’;
    RECONFIGURE WITH OVERRIDE;
    GO

    It retured error of:
    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51. The configuration option ‘backup compresion defulat’ does not exist, or it may be an advanced option.

    Thanks,
    Edwin

  7. This is a different subject.
    I have some very large table which I will like select all the data in those tables to different tables by using select into. Also, I want to set the row count to 100000 at a time until all the rows a select into the new table with a loop. Here is the code I have and it is not working properly. Can you help?

    SET ROWCOUNT 100000
    DECLARE @RCOUNT INT

    SET @RCOUNT = 10000

    WHILE @RCOUNT = 10000
    BEGIN
    BEGIN TRAN
    INSERT Tbl_archive2
    SELECT * FROM archive

    SELECT @RCOUNT = @@rowcount
    COMMIT TRAN
    CHECKPOINT
    END
    go

  8. Pingback: SQL SERVER – Fastest Way to Restore the Database Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – What the Business Says Is Not What the Business Wants Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup « SQL Server Journey with SQLAuthority

  11. Hi… Pinal,
    Before implementing backup compression, i thought of going through Microsoft website… and i found this info..
    The following restrictions apply to compressed backups:
    Compressed and uncompressed backups cannot co-exist in a media set.
    NTbackups cannot share a tape with compressed SQL Server backups.

    Can you please explain, what does it mean….

  12. Hi,
    I recently used the backup in my project so did some R&D and below are the results for Windows compression v/s DB compression:

    I used a DB with no image data and take 2 backups one with compression and another without.
    DB backup w/ compression – 6.49 MB
    DB backup w/o compression – 48.2 MB
    Now when I applied windows compression i.e. winzip the results were:
    DB backup w/ compression – 5.53 MB
    DB backup w/o compression – 4.99 MB
    I made my choice to go with normal backup and then compress it with winzip :)

    Thanks
    Anki

    • Ankit,

      Winzip won’t work with heavy files like files of 40 – 50 GB or more.
      Also, it’s a dual overhead. Means, you take a regular backup which takes time and then you zip it using winzip which again takes time. If you want to restore the database, you unzip the file (time taking process) and then restore (time taking process again).
      If you take a compressed backup it’ll restore faster than the normal backup.

      My experience:
      Backup file size w/o Compression: 250 GB. Backup time around 2 hours.
      Restoration time around 2 hours.

      Backup file size with Compression: around 60 GB. Backup time around 30 minutes. Restoration time around 30 minutes.

      Regards
      Mayur Kashikar

  13. Hi..

    Is there any feature that allows us to set the compression level as well? (Compression level as “Lowest, “Normal” or “Highest”). I ask this because i was just going through a utility (for SQL db backup) that allows me to select either of the mentioned compression level settings.

    Please note that the utility provides me a “.zip” file as backup. So I am not sure whether this utility is using the compression feature of SQL or the WinZip.

    Request you to please throw some light.

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s