“When I use backup compression, I notice the backup file is very large but then once the backup is complete the file is much smaller. Is this how it is supposed to work?”
In the past few weeks I have seen this question pop up a number of times, so I thought I would write a short note about it.
Short answer….. YES.
This is done as the backup file is initially preallocated to a certain percentage of the db size. This is a performance enhancement to keep from having to grow the file over and over. It is explained in KB 2001026.
The article also points out that trace flag 3042 can be used to change this behavior, but there may be a performance hit to the backup operation. As always, test before making any chnages to a production environment.