SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup

I recently received following question from reader. I would like to share the complete story in few short sentences with you to give you complete idea. Let us call the reader Margie. Our long email conversation is converted into chat like conversation

Margie: Hi Pinal – I am seeing strange behavior with regards to my database backup.
Pinal: What is the exact issue?
Margie: I am taking database backup with following script for more than an year and my database is of always certain size. From last six days the size of the database backup is reduced big times. There is absolutely no change in the T-SQL code. It has been the same and part of the backup job for ever.

BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\Backup\ad2'

ad1ad2 SQL SERVER   Effect of Compressed Backup Setting at Server Level on Database Backup

Pinal: Have you deleted any data from your database or moved data to different file-group which is not backed up?
Margie: No. It is same. The amount of the data is ever increasing.
Pinal: Have you restored the database back and checked the .MDF and .LDF file size?
Margie: Well, I tried it now, it is the same.
Pinal: Have you enabled database compression?
Margie: No, I have already mentioned that I have not changed T-SQL code from ages.
Pinal: Have you touched server level settings for compression?
Margie: What is that?
Pinal: Ahha… Gotcha!

Well, after a quick research we figured out what was changed in the server. Their new Admin has changed following settings on the server level, which resulted in the compressed backup.

Their new Admin had read my following articles.

After reading the articles, he checked all the backup settings and realize that there is no database backup configured. After running necessary checks, he decided that compressed backup is the recommended option for all the databases. As it would take time to identify and figure out all the place where the backup code is used and enable compression, he went ahead and configured the server level settings for backup compression. This resulted that all the backup on the server without any T-SQL backup code change were compressed backup. As he had not communicated this with Margie, she was confused with situation.

serverbackupsetting SQL SERVER   Effect of Compressed Backup Setting at Server Level on Database Backup

Well, there are two learning we all adopt from this scenario.

  • Server level backup compression settings affect all the default backup database scripts
  • Database Admin should communicate all of their changes to relevant team member and proper documentations should be done
  • Bonus Learning: Ask around!

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

12 thoughts on “SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup

  1. Hi Pinal

    We had a problem with space issue on server.we were receiving low space ticket .
    backup retention is one day. we had clean up task after the backup completed.
    but during the backup we use have a space crunch due to which the ticket was generated.
    I have tried the backup compression on this server but no luck.
    I have a question here

    Will the backup file will be same as non compressed backup as soon as it finishes the backup process and just before the compression ?

    Or does sql server compress the backup data during the backup process.

    [email removed]


  2. Pingback: SQL SERVER – What is Piecemeal Restore – Quiz – Puzzle – 22 of 31 « SQL Server Journey with SQL Authority

  3. I am using SQL 2005 Standard edition 64 Bit, I have 28GB Database and it takes 35minutes to backup on local disk. I didn’t change any setup configuration or hardware configuration in this server.. It took only 10 minutes 10 days back. I tried to Restore same backup on another server running on SQL 2005 Standard Edition 32 Bit. and Backup successfully completed within 10 Minutes on local disk. Rest of application processing done perfectly on my 64bit server except Backup procedure consumes lots of time. Can anyone help me to solve out this matter.



  4. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  6. Hello Sir,

    Please tell me how to Create new database on other server by Restoring compressed backup file. As Compressed backup file is not accepted in the Restore utility.


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