What is the most effective way to compress and store a SQL Server backup?

Question

I've been doing some testing of different methods for compressing and storing SQL Server backups (using SQL Server 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

I'm not worried about the physical storage or tape drives or anything, just trying to turn our 3TB of data and log files into the smallest single file I can.

So, for example, would a .zip or .7z? Or are there too many variables within my database to be able to accurately estimate what will be the most effective and I'll just need to do some tests? Or is SQL Server's internal compression the best I'll get?

Answer 1

I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

Since you are using SQL 2008 R2 Enterprise edition, you can/must leverage

• Data Compression - Row and Page compression. at Data level

• Backup Compression at taking backup to minimize the disk footprint of the backups.

  USE master;
  GO
  EXEC sp_configure 'backup compression default', '1';
  RECONFIGURE WITH OVERRIDE;
  

Backup compression uses CPU cycles to compress the data before it leaves the server, and that’s why in the vast majority of scenarios, compressed backups are faster than uncompressed backups.

Note that when you use Open source tools you need to uncompress the database backup file before you can start the restore process it self.

e.g: When you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

• 5 Gb for the zip file
• 50 Gb for the backup file
• 50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.

You can still use opensource compression tools like gzip , 7Zip, bzip2 or QuickLZ after the backup compression to benefit.

Also, have a look at MSSQL Compressed Backup on codeplex.

Good references for comparison stats

• File Compression in the Multi-Core Era.
• comparison of compression methods for database dumps

Answer 2

In terms of backup compression, I did (a couple of years ago) make a comparison of the backup compression options provided by Red Gate's SQL Backup, Quests's LiteSpeed for SQL Server, and Idera's SQLSafe, benchmarking the three products. The differences in a typical backup at maximum compression was about a 5% spread between the three for time taken, and a somewhat wider spread for backup size, with Red Gate coming out on top (90% compression vs 80 & 85% for Idera & Quest, in that order).