Compressed backups in SQL Server 2005

Question

What is the best free way to get your maintenance plan-generated backups compressed?

I know there are non-free tools that will compress the backups, but I'm not interested in them.

Options:

1. Have a T-SQL task after the backups that will run a script through xp_cmdshell that compresses every non compressed backup.
2. ???

Any other ideas welcome, I'd like to avoid writing a script/program.

Answer 1

I'd schedule a task after the backup to do it as you already suggest. Also, I believe SQL2008 (at least some flavours) will do compressed backups.

However, if you are at the point where you NEED to compress backups for disk-space reasons, I'd suggest looking at more storage. Unless you are trying to keep the sizes down for a tape transfer, of course.

Answer 2

C:\Program Files\Support Tools>compress /?

is a command line tool for zipping files.

I don't know how to compress only uncompressed files, but you may already know the file names in your T-SQL task?

If you do, you can pass file names as parameters to your xp_cmdshell call.

Answer 3

Idera which ships a non-free backup solution SQLsafe for compressing and encrypting also has a free version of their tool that will do compression called SQLsafe Free Edition. It is meant to get people using the basic features so that they upgrade later when they need more. But the free tool may be perfect for you:

http://www.idera.com/Products/SQLsafefree/Default.aspx

Answer 4

You need three components. A scheduler should launch backup, compression and perhaps other steps like copying the end result to a file server. You can choose your tool for each task but I can recommend our Cobian Backup based setup that has served well for a couple of years.

• Scheduler: Cobian Backup

• Backup: ExpressMaint (for MSSQL Express)

• Compression: Cobian Backup

Basically, you set up backup scheduling with Cobian, run ExpressMaint or other MSSQL backup tool as a Cobian "before event". Cobian then zips the result and saves it to a destination.

Compressing SQL Server backup files is necessary if you intend to move them in a network and/or centralize their storage.

Answer 5

I'm the author of an open source project (GPL v3) that might be what you are looking for (It's not maintenance-plan based though). It is a command line tool (to schedule in Scheduled Tasks in Windows) and backs up to gzip, zip (specifically zip64 due to the 4GB limitation with regular zip files), or bzip2 files. You can use the tool to restore directly from the compressed files, or you can uncompress the compressed files (which are standard *.bak files) and use SQL Server to restore.

It's called SQL Server Compressed Backup, found here: http://sourceforge.net/projects/mssqlcompressed/

A sample command would be:

msbp.exe backup "db(database=model)" "zip64" "local(path=c:\model.full.bak.zip)"