9

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?

closed as off-topic by Paul White, Mat, Mark Storey-Smith, dezso, RolandoMySQLDBA Jul 11 '13 at 17:57

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Physical storage is the driving factor for this, because our hard drive space is running low. However, I wanted to steer away from discussion about the type of RAID I'm using or the answer being "Just get more platters", since those are things I'm already working on, but are long term solutions. – Sean Long Jul 11 '13 at 13:03
  • This seems like something you could test, since it will be largely dependent on the nature of your data. Backup a database with compression, and then try to compress the backup file further using other compression tools. Personally I can't imagine you'll squeak enough extra space to make it worth complicating the process, and don't forget that more compression = more CPU which sometimes = more time. So if it takes an extra minute to save an extra 100 MB of disk space, is that going to be worth it when you're scrambling to restore? – Aaron Bertrand Jul 11 '13 at 14:35
13

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

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

  • 3
    If you compressed your backups via SQL compression, you won't be able to get much compression if you attempt to zip/7zip/rar the backup file. – user1207758 Jul 11 '13 at 19:09
8

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).

Not the answer you're looking for? Browse other questions tagged or ask your own question.