| Author |
Topic |
|
avkravi
Starting Member
8 Posts |
 Posted - 2003-05-23 : 00:17:46
|
| Hi All,I want to make use of the command line zipper through xp_cmdshell procedure. The database backup file will be taken from the source directory and after zipping, it should be sent to a destination directory. Any help in this regard is highly appreciated as I have to make the necessary process and send it to the client so that the weekly full backup will be mailed to my end directly and the same has to be restored for auditing purpose.The matter is urgent.RaviRavi |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-23 : 00:34:43
|
| What part are you having problems with ?Damian |
 |
|
|
avkravi
Starting Member
8 Posts |
Posted - 2003-05-23 : 01:18:04
|
| Hi Damian,Thanks. I was a little bit confused with the way i have to use the command line zipper commands.By manual process we will be using like this:wzzip <destination zipfile> <source database backup file>Finally we will be getting the zipped file <zipfile.zip>.The same process is to be used with the system procedure ie., xp_cmdshell.What are the necessary changes to be affected with respect to setting the path for command line zipper. When installed it will sit by default in C:\Program Files\Winzip folder. But i should execute the commands for zipping from anywhere ie., if i am at the root or at the next higher level.Hope this makes you clear.Waiting for a solution.Ravi |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-05-23 : 01:45:44
|
| So you need help with xp_cmdshell syntax ?It would be something like this :Exec master..xp_CmdShell 'wzzip C:\sqlbackup\northwind.bak C:\dump\nothwind.zip'Damian |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-23 : 01:52:10
|
quote:
What are the necessary changes to be affected with respect to setting the path for command line zipper
Ravi,Do you know DOS?Goto (pun intended) the DOS shell and type in.. PATH /?I leave it up to you as a learning exercise to figure out the rest...DavidM"SQL-3 is an abomination.." |
|
|
|
avkravi
Starting Member
8 Posts |
Posted - 2003-05-23 : 02:47:01
|
| Hi Damian,I tested the query on a backup file of size 977kb and lapsed 5 mts and still executing.Hi DavidM,I am not a guru of DOS, but i can use some commands. I have tested the path /? command. I have set the path to C:\Program Files\Winzip as the command line commands (wzzip & wzunzip) are residing in the Winzip folder. So if u can help me out, it will be very helpful. Otherwise if i can use the GUI of winzip thru a stored procedure it will be very helpful. But how to do it?The ultimate requirement is like this.My Source File is in D:\Database Files\<the respective database backup file.bak>My Destination File should be in D:\Database Files\ZipFolder\<zipped file name.zip>I want to execute this from a procedure with the use of xp_cmdshell procedure, so that in turn i will catch hold of this zip file and mail it. This is have to implement at the client side, so that the zip files will be mailed to me for restoration and necessary audit purpose.Hope this makes the concept more clear.Waiting for a solution.Ravi |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-23 : 08:18:10
|
| test merkin's syntax again, but use a 1 K text file, just to confirm whether WZ seems to be hanging when executed from cmdshell, or there was another issue.Ensure that the user running SQL has the necessary auth to create the file on the destination.Ensure that Directories you are targetting for destination exist.review WZ to ensure that there isn't some "silent" option required .If none of that works, try creating a .bat file to does the WZ. Test it. Try and run the bat file from CMDshell.HTH*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
avkravi
Starting Member
8 Posts |
Posted - 2003-05-24 : 00:38:34
|
| Hi Wanderer,Thanks for the reply. I have tested Merkin syntax and no success. I am using the default "sa" login for SQL Server.The target destination directory is existing.There is no silent option present for Winzip command line operation.As i want to pass the database backup file dynamically, how can a batch file be useful in this scenario.Waiting for help.Ravi |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-24 : 00:55:53
|
From Winzip 8.1 SR-1 command line helpquote:
wzzip [options] zipfile [@listfile] [files...]-yb[c] Automatic, non-interactive ("batch" mode) handling of prompts. If a prompt is issued, the operation terminate with error level 250. Use the optional c suffix to automatically continue with a "yes", "ok" response instead of terminating.Copyright © 2000 WinZip Computing, Inc. All rights reserved.
|
|
|
|
avkravi
Starting Member
8 Posts |
Posted - 2003-05-24 : 01:58:57
|
| Hi ValterBorgesThanks. Can u elaborate?Ravi |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-24 : 10:48:35
|
| Testing done with trial and registered winzip 8.1 w/ command line add-on.Are you using the evaluation version because if so it's a problem they add a thank you for trying message which requires interaction (no way around). To see this create yourself a zip.bat file with the following line.CALL "d:\program files\winzip\wzzip.exe" -a -ybc "D:\Database Files\ZipFolder\%1" "D:\Database Files\%2"save the file to D:\zip.batNow run a command line of d:\zip.batnotice any trial message?If not then you can now call this from QAexec xp_cmdshell 'd:\zip.bat test.zip test.bak', NO_OUTPUTwhere test.zip is the name of the zip file you want to create and test.bak is the name of file you want to zip.If you're version is registeredand you don't use the NO_OUTPUT statement you should see something like this for resultsNULLC:\WINDOWS\system32>CALL "d:\program files\winzip\wzzip.exe" -a -ybc "D:\Database Files\ZipFolder\test.zip" "D:\Database Files\test.bak" WinZip(R) Command Line Support Add-On Version 1.0 (Build 3181)Copyright (c) WinZip Computing, Inc. 1991-2000 - All Rights ReservedSearching... ... .. Adding test.bak.. creating Zip file D:\Database Files\ZipFolder\test.zipNULLIf you use NO_OUTPUT you shoud just see The command completed successfullyEdited by - ValterBorges on 05/24/2003 11:27:52 |
|
|
|
avkravi
Starting Member
8 Posts |
Posted - 2003-05-26 : 00:30:35
|
| Hi ValterBorges,Thanks for the help. I could do exactly the steps you briefed me. As my Winzip version and the add-on support for command line are evaluation version, i will convince my superiors and try to procure the registered version of the same and check the same once again.But the evaluation version executed at the dos prompt is working fine but in QA it is not and taking too much time.Once again thanks for your timely help and if needed i will try to disturb you once again. Pl don't mistake me otherwise.Ravi |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-26 : 11:29:04
|
| Only difference between typing in a command at the command line and executing via xp_cmdshell is the user it runs under and that xp_cmdshell can't interact.If the eval version works ok and doesn't give out any prompts then it should work from xp_cmdshell if the user (probably the sql server service user) has the correct profile.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-05-26 : 15:49:08
|
| The eval gives an eval message and ask's to press control-c. The xp_cmdshell is not taking long it's hung up on the eval message.there are free alternatives to winzip.Try www.download.com |
|
|
|
|
Backup file to be zipped