page contents

About the Post

Author Information

HOW TO: Backup SQL Server 2008 with Compression

This is one of the many new features that I really enjoy with Microsoft SQL Server. Backup compression was introduced with SQL Server 2008 and in my opinion, it is a must tool for most SQL Database administrators. It is especially true when you experience disk space problems.

In addition to saving space, it also decreases the network traffic load and network transfer time of SQL backup data files if you have to copy your SQL backup files. In the past many companies had to buy a third-party backup software compression tool in order to minimize the size of their SQL backup files, however there was also one significant disadvantage with this feature. Compressing database backup files requires additional CPU overhead, so you would need to factor that into your strategy. It is recommended (as usual) to test before going into production with your database backup and restore procedures, to verify the impact that backup compression will bring on your current processor load.

Most SQL Server database administrators will use the default SQL Server Management Studio (SSMS) for backup and restore operations on SQL Server databases. The default settings for your SQL database server will moe than likely depend on how it was configured during setup. In order to see what is your current compression behavior, you will need to run the sp_configure command on the your SQL Server instance, type sp_configure as seen below:

As you can tell from below my backup compression is set to 0, which means it is off.

To enable compression as a default behavior or to change your current setting, type one of the following commands listed below:

With that being said, lets go see the difference that database backup compression can make on your drive space. In this first run, I am going to leave the server as it currently is and just do a backup. Right click any database, then go to Tasks, and select Backup (as seen below):

Now, I will name this database backup as default-is-0.bak so we can clearly identify it later. (This is where the backup compression was not activated by default.)

Also ensure under the Advanced section that we are using the default server setting:

Now lets turn compression on as the default server setting:

I will do another backup, but this time lets call it default-is-1.bak (again) so we can identify it later. (This is where the backup compression was activated by default.)

As you can see below, the difference is noticeable, even on a small database like AdventureWorks:

Keep in mind that you can also use compression, regardless of your SQL Server settings by specifying it in your backup job. You can use T-SQL scripts with the BACKUP DATABASE command WITH COMPRESSION or WITH NO_COMPRESSION statements for managing backup compression in your SQL Server 2008 environment.

Here are some sample TSQL commands which you can use:

Tags: , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.