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

Question

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 can backup databases with SQLBackupAndFTP software. It's a simple UI tool with ability to execute and schedule backup jobs (full, diff and log tran backups). It just compresses backups with embedded archiver or 7-zip and send to a local folder or to a NAS drive or FTP or a to a cloud (Dropbox, Google Drive, Amazon S3). Also there is support forum.

Answer 2

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 3

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