SQL Server 2008 - how to zip backup files and move to remote server

Question

Hi,

I have the non-enterprise edition of SQL Server 2008. I do nightly backups, manually zip the files and then manually copy to a remote server.

I need to automate this using batch files. Copying files from server to server is easy but how do I automate the zipping of the backup first?

The full process I need is:

1. Run the backup nightly
2. Zip the backup to reduce size (with a unique zip filename)
3. Move the zip file to a remote server which is setup as a network drive on the database server

I confess the compression part has thrown me off. Any advice would be very much welcomed.

Thanks in advance.

Answer 1

You could (and should!) most definitely investigate the SQL Server maintenance plans.

These allow you to automate things like

• checking for database consistency
• rebuild indexes as needed
• do database and log backups (definitely use the SQL Server 2008 backup compression!!)

I'm not sure if they have built-in support for zipping and copying to a remote server, but you could definitely automate the backup part with a maintenance plan, and the rest with a command file of some sort.

Answer 2

you do not specify the zip utility that you are using. There are many, but I tend to use Winzip as that is the main zip tool used at work. Winzip has a command line interface ( http://www.winzip.com/prodpagecl.htm ) that is a free addin to winzip that can be called from a command line.

Another alternative would be to use cygwin and tar.gz via the command line.

Answer 3

You can ZIP stuff from the command line, for example with RAR. Just add the ZIP commands to wherever you do the copying. If that's in T-SQL, you can execute a ZIP command using xp_cmdshell.

For a luxury option, check out Red Gate Backup, it makes this process fairly painless.

Answer 4

If you are just stuck on how to compress from a batch script:

1. Install 7-Zip
2. Run from the command line:
   "C:\Program Files\7-Zip\7z.exe" a -t7z MyBackups.7z [Files To Zip]

To get a unique filename, I usually embed the date/time: yyyymmddhhMMss-backup.7z

Answer 5

Since you've got 2008, you've got Powershell installed. I would suggest looking at a psh script executed after a successful backup to compress and copy over the wire. This would most likely be a second job step after your backup.

You can also go old-school and write a batch file to do the compress and copy. Then invoke that as a cmdshell job step, again after your backup job/step.

Answer 6

if you are a programmer you can make an application that get your db backup by SMO and zip this file to .gz file by available libraries.