Backup Compression in SQL Server 2008

Backup Compression in SQL Server 2008

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
How many times have we backed up a database and then we have tried to minimize the size of the backup set by compressing it with an archiving utility?

I am quite sure that such cases were too many ๐Ÿ™‚

SQL Server 2008 introduces Backup Compression. This enhancement allows us when backing up a database to activate backup compression which is performed on the fly.

To do this you have to right-click on a database, select Tasks, then select Backup. In the Back Up Database dialog which appears after this, if you go to the Options tab, you will notice that there is a feature on the bottom of the dialog which says Compression. Then you can set the backup compression for the current database. To this end you are presented with the following options:

1. Use the server default setting
2. Compress backup
3. Do not compress backup

If you select the Compress backup or Use the server default setting and your instance of SQL Server is setup to compress the backups by default, when you initiate the backup process the backup set will be automatically compressed thus achieving high compression rations and reducing the disk storage needed for the backup set.

Note: If you have performed a backup operation on the database before and you have not used the backup compression option but now you are trying to use it you will get an error. Also, if you have used the backup compression before and now you are not using it when backing up the database, again you will get an error. The reason for this is that by the time you have a non compressed backup set, you cannot add a compressed backup to this and vice versa.

One solution to the above restriction is to use the option Backup to a new media set and erase all existing backup sets. This will allow you to proceed with the backup but it will erase the previous backup sets for the specific database. To this end, it is always better to decide the backup method to be followed for each database before you start taking backups of it.

Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found atย Artemakis's blogs can be found at: SQLNetHub.comย and