Unable to zip the backup data

Question

Post reply

Unable to zip the backup data

saidapurs

Hall of Fame

Points: 3477
More actions
August 31, 2012 at 4:19 am

#263627

Hi All,
I am unable to zip the backup data. In my server i installed the 7z (zip software).
My requirement is once backup happen automatically zip the backup data. I am not able to do through script.
Please help me on this.....
Thanks in advance.
Satish

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I am not able to do through script.

What script have you tried? It's quite easy to use the 7zip command line.

John

saidapurs Hall of Fame Points: 3477 More actions · Answer 2

Hi Jonh,

Below script i was used but i am sure this is wrong script.

DECLARE @pathName NVARCHAR(512)

SET @pathName = 'C:\Backup' + Convert(varchar(8), GETDATE(), 112) + '.bak'

Thanks & regards

Satish

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Satish

Since the script doesn't have "7zip" in it, it's not going to get you very far. Search for "7zip command line" to get the syntax. How are you doing this - stored procedure, batch file, job step? Is xp_cmdshell enabled on your server? My recommendation would be to create an SSIS package.

John

saidapurs Hall of Fame Points: 3477 More actions · Answer 4

Hi John,

I need to fix it as a SP, can any one share me exact script please.

Thanks

Satish

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

No. We're volunteers, and we're not here to do the job that you're paid to do. We're all perfectly willing to help out if you've made the effort to sort it yourself and have come up against a specific problem.

John

Potso SSChasing Mays Points: 657 More actions · Answer 6

set FILETOZIP=c:\ue_english.txt

set TEMPDIR=C:\temp738

rmdir %TEMPDIR%

mkdir %TEMPDIR%

copy %FILETOZIP% %TEMPDIR%

echo Set objArgs = WScript.Arguments > _zipIt.vbs

echo InputFolder = objArgs(0) >> _zipIt.vbs

echo ZipFile = objArgs(1) >> _zipIt.vbs

echo CreateObject("Scripting.FileSystemObject").CreateTextFile(ZipFile, True).Write "PK" ^& Chr(5) ^& Chr(6) ^& String(18, vbNullChar) >> _zipIt.vbs

echo Set objShell = CreateObject("Shell.Application") >> _zipIt.vbs

echo Set source = objShell.NameSpace(InputFolder).Items >> _zipIt.vbs

echo objShell.NameSpace(ZipFile).CopyHere(source) >> _zipIt.vbs

echo wScript.Sleep 2000 >> _zipIt.vbs

CScript _zipIt.vbs %TEMPDIR% C:\someArchive.zip

pause

a batch file like this might work. you will need to customize it obviously but it might work. then you could put a watch on the folder that looks for files that are newer than say 12 hours nad have it zip the file.

Lowell SSC Guru Points: 323518 More actions · Answer 7

here's another model for you to use as well:

--http://www.7-zip.org/download.html

--http://downloads.sourceforge.net/sevenzip/7za920.zip

--xp_cmdshell is limited to ONE set of double quotes.

--one of the paths needs to not contain spaces!

--see 2003 MS KB http://support.microsoft.com/kb/266224

DECLARE @results TABLE(results varchar(255))

declare @command varchar(2000)

--zip one file

SET @command =

'"C:\DataFiles\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!

+ '7za.exe"' --the exe: i'm using in the command line utility.

+ ' a ' --the Add command: add to zip file:

+ 'C:\DataFiles\' --path for zip

+ 'myZipFile.zip' --zip file name, note via xp_cmdshell only one pair of dbl quotes allowed names!

+ ' ' --whitespace between zip file and file to add

+ 'C:\DataFiles\' --path for the files to add

+ 'SandBox_2011-07-25.bak' --the file

+ ' -y' --suppress any dialogs by answering yes to any and all prompts

print @command

--"C:\DataFiles\7zip_CommandLine_7za465\7za.exe" a C:\DataFiles\myZipFile.zip C:\DataFiles\SandBox_2011-07-25.bak -y

insert into @results

exec xp_cmdshell @command

select * from @results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saidapurs Hall of Fame Points: 3477 More actions · Answer 8

Hi Poste & Lowell,

Thanks for responding, i will test your script and revert back

Thanks

Satish

saidapurs Hall of Fame Points: 3477 More actions · Answer 9

Hi Lowell,

Thanks a lot, my issue has been resolve and i have small doubt i am not able to same name on zip file.

Please advice me how to fix it..

Thanks in advance

Satish

Lowell SSC Guru Points: 323518 More actions · Answer 10

saidapurs (9/3/2012)
Hi Lowell,
Thanks a lot, my issue has been resolve and i have small doubt i am not able to same name on zip file.
Please advice me how to fix it..
Thanks in advance
Satish

Youll need to be more specific. Are you getting an error when you createcthe zip file, or you are worried about naming x.bak to x.zip? If the file already exists the zipxwould overright the existing, but the .bak file would remain untouched. Zip cipes the file and compresses it. It doesnt affect the original file.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saidapurs Hall of Fame Points: 3477 More actions · Answer 11

Hi Lowall,

I am worried about file name. Yes your script is working fine, let me know how do i change it.

Thanks

Satish