SQL Backup Compression Default Option in MS SQL Server 2008 sp_configure Command

SQL backup compression is a must tool for many Microsoft SQL Server administrators if you experience disk space problems.
Besides, SQL Server backup compression 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 company network.
Many companies buy third-party backup software compression tools in order to minimize the size of their sql backup files for such reasons.

Enabling SQL backup compression is one of the top 10 tips for optimizing SQL Server performance and tuning SQL Server performance steps.

Of course, besides the advantages of using backup compression there is one significant disadvantage.
Compressing database backup files requires high CPU utilization during compression time. If the SQL Server machine has a powerful processor or you have protected and reserved CPU time for other processes running on the server using a tool like Resource Governor, during database backup other processes do not get effected negatively. At the end even though compression consumes additional CPU time, if you look at the overall benefits database backup compression consumes less CPU cycles since less data is written od disk and less disk IO takes place. Backup compression option is a must for increasing SQL Server performance for database administrators and is a good method of effective resource using.
If you experience CPU problems on your server during database backup and sql database restore, then to prevent the nagative effects of backup compression like additional processor load for the database server, consider using a tool like Resource Governor for stable SQL Server performance for other applications running on your server.

Microsoft SQL Server database administrators that use SQL Server Management Studio (SSMS 2008) for SQL backup and restore SQL Server databases, can define the default backup compression behaviour of MS SQL Server 2008 while SQL Server backup process.
This means a SQL Server DBA can configure Microsoft SQL Server 2008 instance to take all sql backups in compressed mode.
So unless the sql backup operator states the opposite, using default settings SQL database administrators can state that for all SQL Server 2008 backup files sql backup compression will be active.

In order to see, what is the default sql backup compression behaviour run the sp_configure command on the related SQL Server instance.


sp_configure

Code

A list of SQL Server 2008 configuration settings and parameter values will be displayed as shown in the below list.
Please pay attention to the second item in the SQL Server configuration settings which is the "backup compression default".
The run_value column for the SQL Server backup compression default value controls the sql backup compression preferences.

sp_configure-backup-compression

SQL Server backup compression default option can be set and altered using the sp_configure command as follows:

To disable backup compression by default:


sp_configure 'backup compression default', 0

reconfigure

Code

To enable backup compression by default:


sp_configure 'backup compression default', 1

reconfigure

Code

Now let's a few examples and see how the default backup compression settings will affect the size of the sql database backup files.
Open context menu on the sql database by right-click, select "Tasks > Back up ..." from the menu items as shown in the below figure.

Change the destination of sql backup to "backup-with-backup compression default-is-0" which states that the backup compression is not activated by default.
After you named the database backup file, execute the backup task.

sql-backup-destination

C:\SQLDatabases\backup-with-backup compression default-is-0

As you see the in the Options tab of the Backup process, in the Compression section the value of the "Set backup compression" setting is "Use the default server setting".

So without any changes to compression settings, first change the "backup compression default" sp_configure parameter to 1, so activate sql backup compression by default.
Then name the database backup file to "backup-with-backup compression default-is-1" to indicate that backup compression is on and take a new fresh database backup.

C:\SQLDatabases\backup-with-backup compression default-is-1

sql-backup-destination-for-compressed-backup

In the below screenshot, you can see how it differs to use SQL Server backup compression with MS SQL Server 2008.
Of course the ratio between compressed and not-compressed backups will differ to the table structures of your databases, but it is obvious that compression will minimize the backup file sizes.

SQL administrators can also take database backups using sql backup scripts.
T-SQL BACKUP DATABASE command has now WITH COMPRESSION and WITH NO_COMPRESSION hints for managing backup compression in SQL Server 2008 environment.
Here are some sample sql commands to take database backups that you can use.


-- SQL Backup Database with sql backup compression on

BACKUP DATABASE MySQLSamples

TO DISK = 'C:\SQLDatabases\MySQLSamples-with-compression.bak'

WITH COMPRESSION;



-- SQL Backup Database with sql backup compression off

BACKUP DATABASE MySQLSamples

TO DISK = 'C:\SQLDatabases\MySQLSamples-with-no-compression.bak'

WITH NO_COMPRESSION;



-- SQL Backup Database with sql backup compression by server default

BACKUP DATABASE MySQLSamples

TO DISK = 'C:\SQLDatabases\MySQLSamples-with-default-compression-configuration.bak';

Code

As you can see in the below screenshot of the target destination folder where sql backups are stored, the compressed backup sizes are one fourth of normal backup files.
And for this example the default SQL Server 2008 backup compression option is set to 1.

sql-server-backup-database-files