I was surprised today when doing a review that a client was still under the impression that backup compression was still an enterprise feature.  No no no no no.  It’s there for everyone with standard edition since SQL Server 2008 R2 and there’s really no reason not to be using it.  The only real question is why is it not a default setting?

Option Name:  backup compression default

Usage:  sp_configure ‘backup compression default’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  Who wants smaller, faster backups?  Everyone!  Backup compression is very impressive and can reduce your backup sizes considerably.  On average I would say you can expect your compressed backup to be about 40% of the size of a non compressed backup, but some data is going to compress better and some not so well, so your mileage will vary.

When should I use it:  Almost always!  Not only are you going to get a smaller final backup size, but because the IO is reduced the backups are likely to complete faster than a regular backup.  Now, it’s worth noting that there is compression going on here, and that means extra CPU.  If your CPU is already being heavily taxed then you may want to steer away from backup compression as it will add to your processor load.

What else should you know:  There’s a few other bits that might be helpful with the ‘default backup compression’ option.

• It’s only a default – It can be overridden in the actual backup command.  So if you want to leave it disabled, but find yourself short on space to take an adhoc backup – you can specify COMPRESSION as a backup option and your backup will be compressed.
• You cannot mix compressed and non compressed backups within the same device.  I don’t know how common it is to append backups anymore.  I certainly don’t see it a lot.  But if you do that then you’ll need to remove\rename the old backups before switching from uncompressed backups to compressed.
• Restore syntax is exactly the same whether a backup is compressed or not.  SQL just looks at the headers and figures out what to do.
• All the other options you can throw at the backup command remain the same.  For example if you are doing a backup with checksum it works regardless of whether the backup is compressed or not.