MSSQL 2008 Backup compression

Compression of DBs backup is a nice feature that came with MSSQL 2008 and later.

It could be handy in some situation but of course, compression costs resources on the server.

I did some tests with a MSSQL server on which I restored DBs of vCenter and VMware Update Manager from a real production environment.

My MSSQL server has the following specs:

Is a VM = yes

OS = Windows 2008 R2

MSSQL = 2008 R2

CPU = 1

RAM = 3 GB

Local hard disk = 1x SATA 7200

Multi partition = OS, DB Data, DB Logs, Backup repository

User Databases = 2 in single recovery mode

vCenter_DB size =

vupdatemanager_DB size =

For my first test, from the management studio, I’ve just set through a maintenance plan 1 single task of backup and no schedule planned. I will run it manually.

Let’s enter a name

Now we will add the necessary tasks

Drag and drop from the toolbox a

Edit it

Choose the type -> Full; the databases -> user DBs; don’t forget the extension “bak_full_usr_NC“; the verify; the subdir. For the compression I let the default because it is not active at the MSSQL server level.

Let’s save this maintenance plan.

Now I’m going to do another plan with compression enabled

Let’s enter a name

Now we will add the necessary tasks

Drag and drop from the toolbox a

Edit it

Choose the type -> Full; the databases -> user DBs; don’t forget the extension “bak_full_usr_WC“; the verify; the subdir. For the compression, choose “Compress Backup”.

Let’s save this maintenance plan.

First, run the maintenance without compression

The results:

Time to complete – 07 min 29 sec

vCenter_DB_Backup – 6.94 GB

vupdatemanager_DB_Backup – 682 MB

Mainly disk access read on the DB and Write on the backup device (50MB avg. total) and read on the backup device for verify phase (80 MB avg. Read). CPU average of 2% for sqlservr.exe

Now let’s run the maintenance with compression

The results:

Time to complete – 02 min 42 sec

vCenter_DB_Backup – 761 MB

vupdatemanager_DB_Backup – 89 MB

During the backup a high CPU Usage of sqlservr.exe an average of 50% with picks at 100% and important disk access (90MB avg. total).

The first observation is that backup compression drastically decreases backup time and its compression ratio is very good for vCenter and update manager DBs.

In the other hand the compression also increases the CPU usage and disk access of your server.

I would say that before enabling compression I would check the CPU activity of your server if it is suitable. Really use the non-pick usage of you server to run compressed backup. And of course try it on a “small” database first to see if it works fine and progressively compress new databases’ backup.

Of course compression works also for differential and transaction log backups.

If the compression ratio is very good, you might also change you backup strategy to daily full, instead of full + differential

For those who prefer the TSQL way simply add the option COMPRESSION to the BACKUP statement:

Ex:

BACKUP DATABASE [<DB-NAME>] TO  DISK = N'<path to backup folder>\<backup device name>’ WITH NOFORMAT, NOINIT,  NAME = N'<name of your DB backup>’, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10