Search code examples
sql-server-2008command-linesql-jobwinzip

SQL Server Job Using WinZip Command Line

I have a SQL Server 2008 Job that backs up a database, then zips that backup and moves the zipped file. My job runs fine until it gets to the step that calls WinZip, which executes:

c:\program files (x86)\winzip v19.5\winzip32.exe 
      -m \\RemoteShare\RestrictedFolder\dbBack.zip 
      x:\SQLInstanceFolder\BackupFolders\dbBack.bak

The job neither completes nor fails; it just stops moving forward. It will generate the dbBack.bak file and create the dbBack.zip file in the remote location, but it won't proceed past there. It seems to be behaving like it is waiting on a pop-up confirmation, but I don't see one when I log in to the console or run the zip from the command line.

I've tried adding -ybc flag to automatically confirm or skip any prompts, but it didn't seem to do anything. The process still didn't complete. I've even tried to > pipe output of the process, but it won't even write my log file.

This is a secured system and infrastructure, but I'm fairly certain I'm not being blocked by a permission. My SQL Server service account that runs the job has access to the folders it needs and it can run the winzip32.exe process. This process ran fine, but we had to upgrade WinZip this past weekend (19.5), and that's when it stopped working properly. We aren't able to roll back to the previous version (10).

Does anyone have any idea on what could be stopping my process or how to make it proceed?

Solution

  • I think I discovered the problem. It turns out, we are using the GUI version of WinZip and calling the executable from the command line. Even though we can't see the GUI, it's still there. So, the prompt to confirm our compression is still there in the program's workflow, we just can't see it and thus can't confirm it. And the confirm flags don't work with the GUI version.

    enter image description here

    My workaround involved logging in to my SQL server as our service account and running a WinZip operation. When it completed and gave me the Add Complete prompt, I checked Do not display this dialog in the future and clicked OK. This will suppress that prompt when the service account runs its Job.

    If someone changes the service account, we'll have to do this again, so our ultimate solution will be to install the WinZip Commmand Line Plugin. Hopefully, when that's done, we won't have to worry about this.

    But it works now. :-)