Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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.

1  
Are you using Enterprise Edition ? If yes, you can use compression - both data as well as when doing backups .. since you are using sql server 2008. R2 has backup compression in standard edition as well. –  Kin Jul 10 '13 at 18:14
    
I clarified the question a bit. I'm curious about things outside of SQL's internal compression, which I'm currently using. –  Sean Long Jul 10 '13 at 18:17
1  
What about using third party like Redgate SQL backup Pro red-gate.com/products/dba/sql-backup Best compression. I am using it and saves us a lot of disk space. Also if interested, you can try using MSSQL Compressed Backup msbplaunch.codeplex.com –  Kin Jul 10 '13 at 18:23
1  
If the physical storage or tape drives aren't a concern, I'm curious as to what is the concern that compression is needed. There seems to be something missing here. –  Techie Joe Jul 10 '13 at 21:34
    
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

2 Answers 2

up vote 9 down vote accepted

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 comparision stats

share|improve this answer
    
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

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

share|improve this answer

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