THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Backup Compression with SQL Server 2008

SQL Server 2008 provides a feature, which, in my opinion, has been far overdue -- backup compression. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you are off to the races. This bit of code will back up the AdventureWorks2008 database using compression:

BACKUP DATABASE AdventureWorks2008
TO AdWorksBackup

Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog.

You may be asking yourself: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700 MB of disk space, I got a 147 MB compressed backup file. Compare that to the 636 MB file I got when not using compression.

Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the option Compress Backup. Alternatively, you can run the following t-SQL code:

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'backup compression default', 1

The first command enables advanced options and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option.

SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time

Published Thursday, October 02, 2008 4:01 PM by ejohnson2010



flank42 said:

This feature is only available in Enterprise Edition (or Developer), but not Standard

October 3, 2008 8:16 AM

BrentO said:

To echo Flank42's comment - the ability to back up is only included with Enterprise & Developer Editions, but you can restore a compressed backup to Standard Edition.

Data compression, on the other hand, isn't as flexible: it's only available in Enterprise & Developer Editions, and if you try to restore a database with compressed tables onto a Standard Edition, the restore will fail.  Important to know before you install your Disaster Recovery server, that's for sure.

October 3, 2008 9:45 AM

jerryhung said:

It's a feature long overdue, yet MS only enables it for Enterprise edition, which is sad

Staying with Red Gate SQL Backup then for compressed backup

October 3, 2008 11:21 AM

Rupesh Kumar said:

Nice article. But how to restore a compressed backup file?

June 15, 2013 1:26 AM
New Comments to this post are disabled

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Privacy Statement