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'
GO
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.
- Introduction to New Feature of Backup Compression
- Data and Page Compressions – Data Storage and IO Improvement
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.
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 (https://blog.sqlauthority.com)
9 Comments. Leave new
great catch Mr.Dave !!! Thanks for the post!.
Thanks,
Vipul Bhatt
it is gr8!
But pinal I also uses same command for taking DB backup But one thing I am observing is that…
every-time it increases number of files in .bak file…
what exactly it is?
Are you appending your backups to a pre-existing backup set?
Can compressed and non-compressed back ups exist on the same drive
Yes,it can surely exist.
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]
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.
Regards,
Devang
Did you check if there were any other processess running when taking backup?
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.