I want to take a database backup automatically and to zip the database automatically

Why do you want to use ZIP rather than SQL's inbuilt backup compression?

ZIP requires multiple steps to achieve compression, in SQL you just add the compression argument to the BACKUP command

I think the test needs to be using ZIP on an UNcompressed backup. Any file that is compressed will then not compress well thereafter, and SQL's compression is "moderate" as it does it during streaming rather than being able to review the whole file, as a post-process.

I raised the question about compressed backups in this thread:
http://forums.sqlteam.com/t/pros-cons-of-database-backup-compression/2263

SQL's inbuilt compression has significant gains because the output file is smaller it takes less I/O to write it. So although it uses more CPU than a normal backup it uses less I/O which is more likely to be critical on a server.

My tests gave 80-86% filesize saving and reduced backup time by ~40%

If you use ZIP then you have the original backup i.e. 100% filesize and backup time. THEN you have to ZIP the file, so the file has to be re-read, compressed, and re-written. Using normal ZIP compression I got about 2% more saving, which is negligible compared to SQL's compression. Using the most aggressive compression I was able to get 91% compression, but it took 13x as long, so unless filesize is absolutely critical I just can't see the point.

At the other end you have the Restore. A backup file compressed by SQL will restore more quickly (than an uncompressed one) because the I/O is less. For ZIP you have to read, uncompress, write and THEN restore, and the bigger, uncompressed, file will restore more slowly too. Thus your disaster recovery time is greatly increased.

2 Likes