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)
