SQL 2008 Backup Compression

We all know that space on a server is very important. Databases need room to grow. However, space gets eaten fast when you produce backups and store them all on the same drive. In SQL 2005 and earlier, the backups we basically equal in size to the database which means if you have a 200GB database and keep 5 days’ worth of ‘Full’ backups, you are taking up 1TB of space just in backups and this doesn’t include transaction log backups or differential backups. We, as DBA’s, were forced to find ways to reduce the space whether it was by keeping less historical backups, moving them to tape or using more differentials. Whatever way you did it, it was hard to save space. A lot of people went out and bought 3rdparty tools based simply on the fact that they will compress those backups by up to 90%. For those people that opted not to buy those tools, you have been fighting a long battle. So when SQL 2008 came out with the backup compression built in, you were ecstatic, only to find out when you ran a native backup, it didn’t compress anything… what the hell you think, where is the compression? Well, as it turns out, the backup compression does not come enabled out of the box. Why, I am not 100% sure, but it is easy enough to permanently enable it on a SQL instance with this script:

EXEC sp_configure ‘backup compression default’,’1′;

You can also add the ‘with Compression’ to a backup script:

BACKUP DATABASE AdventureWorks TO disk = ‘F:BackupsAdventureworks.bak’ WITH COMPRESSION

Or vice-versa:

BACKUP DATABASE AdventureWorks TO disk = ‘F:BackupsAdventureworks.bak’ WITH NO COMPRESSION

Whatever way you choose by using a 3rd party tool or the built in compression, it will save you space, time and money.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s