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.
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.
[MyDatabase] DISK=N'D:\Backup\Mydatabase.bak'More information about Backup Compression can be found here.
WITH NOFORMAT, INIT, NAME = N'Mydatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
Enjoy using SQL Server Backup Compression.