Dave62 (3/14/2011)
A while ago I implemented an SQL Server job that uses 7zip to compress backups after they run. I've been getting between 80 and 85 % compression.When we upgrade I'll have to test out the built-in compression to see if the percentage is comparable. Either way the job may still come in handy in places that are slow to upgrade.
I can get between 30 to 90 percent compression on databases, depending upon the contents and datatypes (straight text and numbers compress VERY well). And the beauty is twofold -- one, it's only one step instead of two, and the one step is not much longer if at all longer than the original first step of making the backup in the first place, so you might cut your time in half in theory. Second, it doesn't require the Agent to run a command line utility. We did that in 2000 with WinRar and I found that upon occasion WinRar would hang, and so the job would hang and never complete or error out. Not an issue here. We have moved to compressing ALL 2008 backups as a matter of course; even if it is not much space savings, it is some and minimal overhead. A truly great feature to have.