Using xp_cmdshell to unzip a zip file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Using xp_cmdshell to unzip a zip file

Author

Topic

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-02-26 : 06:53:52

Hi,

I have written an automated SQL script to test a stored procedure
but 1 step involves manually unziping a zip file

The stored procedure backups some tables into a DB and it stores the DB as E:\MSSQL\Data\CVTGameplayBackup.zip

If i can unzip this file using a command in SQL or the xp_cmdshell i can get this file unziped and then restore the DB in my script
Restore i know how to do in SQL. Just the unzipping the zip i cannot do.

How can i do this please?

Regards, Riaz

Riaz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-26 : 19:19:19

You would unzip using command line, which is accessible in SQL Server via xp_cmdshell. You must have a compression utility that has command line interface where you can unzip though. I know winzip, pkzip, and winrar all have it. What compression tool are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-03-01 : 04:18:01

I have WinZip Command Line Support Add-On installed on the SQL Server machine

I have tried:
declare @unzip string
set @unzip = 'WZUNZIP -o '+ 'E:\MSSQL\Data\CVTGameplayBackup.zip'
exec master..xp_cmdshell @unzip

Error shown is:
Column, parameter, or variable #1: Cannot find data type string.
Parameter or variable '@unzip' has an invalid data type.
Riaz

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-01 : 11:39:08

string isn't a valid data type. change it to varchar(75)

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-03-02 : 04:33:41

I get this error now.
I have Winzip 9 installed with the Command Line Environment

'WZUNZIP' is not recognized as an internal or external command,
operable program or batch file.
NULL

Riaz

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-03-02 : 06:20:17

I have got it to work this way now.

EXEC master..xp_cmdshell '"C:\Program Files\Winzip\WZUNZIP.exe" -yb -o 'E:\MSSQL\Data\CVTGameplayBackup.zip' E:\MSSQL\Data\'

Thanks for the replies with help. Got it to work in the end.

Riaz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-03 : 16:17:08

I like to put the path in the "path" environment variable so that I do not need to include it in my scripts. That way you can reference the executable without also specifying its path. But because you are running it inside xp_cmdshell, that change wouldn't take effect until after you restart SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources