Best/fastest compression format for (sqlserver) databases?

Question

Has anyone found a good compression format for MS Sqlserver databases? If so, what do you use and are you pleased with how it performs?

My company frequently will compress a database snapshot from one of our clients and download it so we have a local copy for testing and dev purposes. We tried zip in the past, but once the database files crossed the 4Gb boundary we had to use rar (zip is 32-bit only). The problem is rar takes a lot of time to compress, and we don't know if it gives us the best compression ratio either.

This isn't a question about the compression utility so much as the compression format. We use WinRar, but are considering 7zip, which supports a number of formats.

Answer 1

In sql 2008 you have native compression, if you have to do this a lot and don't have SQL server 2008 then take a look at something like Quest LiteSpeed which compreeses the backup automatically

Answer 2

Redgate has a solution for this that is very popular.

http://www.red-gate.com/products/SQL_Backup/index.htm

Answer 3

Thank you for the responses do far. I'll look into your suggestions and see what fits best.

Answer 4

In the no-cost category, newer versions of gzip and bzip2 are supposed to include large file support (someone on the internet tells me that bzip2 1.0.1 and beyond is large file compatible thanks to Cyril Pilsko, while gzip 1.2 can be patched which the dowloadable binaries are, and the gzip 1.3 beta includes support). While I use 7zip on my windows pc for convenience, I tend to prefer bzip2 for speed vs compression.

I have also heard of tricking the non-large-file versions by doing something like cat file | gzip > file.gz. Generally you're trading off time with compression level, but one of bzip2's claims is that it uncompresses very quickly, which in a disaster recovery situation should be your most important metric. In that regard, I believe EMC's tape backup solution (ELM?) used to skip compression on DB partitions by default. Also If you're really serious about packing it into a tiny space, you might try something like rzip, but I've never known anyone to actually use it.

Answer 5

There's always UCL compression and or LZO compression, both are GPLed so be sure you know how you're using them if it is a commercial project.