question

maharayale avatar image
maharayale asked

Backup size just before compression

When backup is on processes the size is more than three times after it completed. The maximum database compression seems takes place at the end of the backup processes. This is a problem for backing big size databases. For instancein my experience for 500GB database, the backup size during taking backup is 171GB and that size drop to 55GB when the backup completed. Any advice how to keep the backup at minimum size during taking the backup?
backup-compression
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Yes. See https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server and use trace flag 3042. TL;DR: it's not that the backup is taken and then compressed, but the backup file is pre-allocated at that size (roughly 1/3rd the size of the DB) and then grown when necessary during the backup and trimmed back at the end of the backup. The compression is done to the data while it is being written out to the backup file, not afterwards.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.