Friday, April 30, 2010

Backup compression in SQL Server 2008 (R2)

What is backup compression?
In the previous versions of SQL Server you can only make an uncompressed backup to disk. The size of this backup is almost the same as the size of the database. To decrease the size of the backup file, you can use compression software like Winzip, WinRar etc. This requires additional CPU power and disk space. As of SQL 2008 a new backup option is introduced called 'Compression' which directly create a compressed backup.

Why using backup compression?

I did some tests with Exact Globe database which resulted in next graph. The uncompressed backup results are set to 100 to compare with the uncompressed results.

  • Decrease in backup size up to 65 %. This depends of course on the content of the data.
  • CPU usage will increase during the backup process. The more data can be compressed the more increase in CPU usage.
  • Faster backup speed (25%) because it requires less disk I/O.

In which SQL editions is this available?Unfortunately backup compression is not available in all editions of SQL 2008 (R2).
In SQL 2008 it is only available in the Enterprise edition.
In SQL 2008 R2 it is available in the Standard and Enterprise edition. 

How to use backup compression?
  • Server level: Backup compression is off by default on server level. If you enable it on server level all your backups will be compressed by default. Use next SQL script to enable by default backup compression on server level.
USE master;
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;
  • Database level: If you do not want to enable this option on server level you can create a backup by making use of the compression option in the backup. In the Object Explorer of SSMS Select Database, Properties, Tasks, Backup, Options, Compression.

Or using a script. In next example for database 'Mydatabase' :  
[MyDatabase] DISK=N'D:\Backup\Mydatabase.bak'
WITH
NOFORMAT, INIT, NAME = N'Mydatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

GO
More information about Backup Compression can be found here.
Enjoy using SQL Server Backup Compression.

3 comments:

TacticalDragon said...

I've read that you cannot have different backup types Compressed and non compressed within a media set.

Lets say i turn on compression at the server level a day before the next full backup. Now in my environment i have full database backups that happen once a week per database, differentials daily and tlogs once an hour.

When i turn on compression at the server level in that scenario i will have quite a few compressed tlog backups and possibly a compressed differential for that last backup.

Would restoring become a problem at that point for me? Or is SQL smart enough to figure out that the last full backup was not compressed, and therefore wont compress the differential and tlogs attached to it? Or will it just blindly start compressing what ever is backed up at that point?

Thank you

TacticalDragon said...
This comment has been removed by the author.
André van de Graaf said...

TacticalDragon,

SQL will take care of that. For instance you can restore a compressed backup on a server which does not support backup compression.

Gr. Andre