Unzip a BAK file from SP?

[mkrausnick]

In thread962-1414133, ptheriault mentioned being able to zip and unzip files from within a SQL Server job. How is that done? Can that be done from within a stored procedure?

The zipped BAK file I get from my vendor is created by a version of Winzip that is not compatible with Windows Compressed Folders, so I have to use the Winzip command line utility to unzip it. Then I run a SP to restore it. It would be great to have just one step instead of two.



Mike Krausnick
Dublin, California

 

[ptheriault]

You can execute windows commands from a sql server agent job. You don't need a stored procedure but If you want to use a stored procedure you'll have to use xp_cmdshell.

From the sql agent job you would add a new step to un-zip the bak file.

The command type is windows operating system.

Then just place the command in window. Or you could put the command in a bat file and execute the bat file from the job.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Yup. Like Paul said.

There is a FAQ on this. faq183-5065
(helpful because it shows some sample code)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[onpnt]

What version of SQL Server are you on? I'm not a fan of xp_cmdshell and was happy to see more than anything CLR in 2005. If you are on 2005 you can write a CLR procedure/function to easily unzip/zip using the compression classes. It would prove to be a bit more stable I think than xp_cmdshell

Not that I would ever go against Paul or George. They are over my head Just a suggestion

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ptheriault]

That's a good point onpnt, I didn't suggest CLR because I haven't used it. But that would be better than xp_cmdshell. Personally, I would just un-zip the file in the job step. It's the quickest and easiest to support.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

I wouldn't recommend using CLR either. Most CLR assemblies are not supported in SQL CLR. The CLR assemblies which SQL does support is a very small subset of the full CLR.

This MSKB doc shows which assemblies are supported in SQL CLR

http://support.microsoft.com/kb/922672/en-us.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[onpnt]

yes. you can register assemblies though and make them available. It's not completely supported yet but I've done it very successfully.

Although in this case the System.IO.Compression class is fully integrated

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[mkrausnick]

Thank you all for the great information. We're currently running SQL 2000 but will be migrating to 2005 next month. I will try the sql agent approach.

Thanks again!

Mike Krausnick
Dublin, California