Zip around 1000 Db's having each 10 Gb size

Question

Post reply

Zip around 1000 Db's having each 10 Gb size

sps-554505

Default port

Points: 1463
More actions
December 13, 2007 at 11:10 pm

#377467

Hi,
I need to zip couple of Db's for the archive process.The server is not having winzip installed,it i shaving the defacult microsoft compressed zip option,using that it is not
possible to zip files more than 2 Gb,is there any other method,I have installed the
winzip evaluation version,
and written the script but it shows 'winzip' is not recognized as an internal or external command,
this is the script
declare @db_name varchar(256),@txt varchar(1024)
DECLARE DB_archive CURSOR FOR
SELECT db_name from DB_List1203
OPEN DB_archive
FETCH NEXT FROM DB_archive INTO @db_name
IF @@FETCH_STATUS <> 0
PRINT 'All DBS Zipped'
WHILE @@FETCH_STATUS = 0
BEGIN
set @txt = 'winzip -a V:\Data_1\'+@db_name +'.zip V:\Data_1\'+@db_name+'.mdf'
Exec master..xp_cmdshell @txt
FETCH NEXT FROM DB_archive INTO @db_name
END
CLOSE DB_archive
DEALLOCATE DB_archive
Please let me know some idea to resolve this,winzip shows -version like this WinZip 9.0 SR-1
or any other method to zip this,using compressed zip what commands wil use
Regards,
Shine

Viewing 9 posts - 1 through 8 (of 8 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Add the full path in your @txt parameter like this:

set @txt = 'C:\Program Files\Winzip\winzip.exe -a V:\Data_1\'+@db_name +'.zip V:\Data_1\'+@db_name+'.mdf'

Exec master..xp_cmdshell @txt

[font="Verdana"]Markus Bohse[/font]

muralijv SSC Enthusiast Points: 162 More actions · Answer 2

I have used MICROSOFT COMPRESS OPTION to compress a 3 GB file it seems to be working fine.

Murali

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

C'mon folks... WinZip Pro costs what, 60$??? Don't screw around with your archive data... do it right. You probably spend more than $60 a month buying coffee, softdrinks, and/or snacks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

sometimes post amaze me! rar is pretty good and costs about the same as winzip and works cmd line well. I hope by microsoft compress you don't mean ntfs compressed folders as those are not certified with sql server.

I'd actually suggest buying litespeed or redgate's tool, both of which produce "proper" compressed backups and not mess with zip tools, use something thta works with sql server.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

jerrymedia Old Hand Points: 328 More actions · Answer 5

HEY,

HERE A PROC THAT i USED FOR COMPRESSING NIGHTLY DATABASE FILE(S) .BAK HOPE IT WILL BE OF HELP TO YOU. NOTE THIS USES COOMAND LINE VERSION OF WINZIP. SO YOU MUST A COPY OF IT INSTALLED. YOU CAN ALSO USE GZIP TO COMPRESS YOUR .BAK FILES. THE GOOD THING ABOUT GZIP IS THAT IT IS FREEWHARE SOFTWARE.

.... IF YOU NEED THE COMPLETE PROC LET ME ME SO i WILL POST IT

Declare @TobeCompressedpathSource sysname -- holds bak files to be zipped eg d:\maint\daillybackup\*.bak---

Declare @CompressedBakFileDest sysname -- holds zipped files eg d:\maint\daillybackup.zip---

Declare @sqlname sysname

Declare @now1 sysname

Set @now1 = (Select convert(varchar,getdate(),112))

Set @sqlname = (Select @@servername)

Declare @path sysname

Declare @Ext sysname

Set @Ext = '.zip'

Set @path = 'D:\maint\Backup'

Set @CompressedBakFileDest = @path +'\'+@sqlname + @now1+ @Ext

--Set @CompressedBakFileDest = @path +'\'+@sqlname + @Ext

--Set @CompressedBakFileDest = 'D:\maint\Backup\Db008bakfiles.zip' -- all bak files will be zippup here---

Set @TobeCompressedpathSource ='D:\maint\Backup\*.bak' -- location for bak file E:\backup\Unzipped\msql --

Declare @wpro sysname

Set @wpro = 'C:\WINDOWS\system32\wzzip.Exe'

Declare @opt sysname

Set @opt = '-rp'

Declare @CmdwinzipM sysname

--Set @CmdwinzipM = @wpro + + @opt +CHAR(9)+ @CompressedBakFileDest + CHAR(9)+ @TobeCompressedpathSource

Set @CmdwinzipM = @wpro +CHAR(9)+ @opt +CHAR(9)+ @CompressedBakFileDest + CHAR(9)+ @TobeCompressedpathSource

Exec master..xp_cmdshell @CmdwinzipM, no_output

--print @CmdwinzipM

...

sqltung Hall of Fame Points: 3962 More actions · Answer 6

You may also want to consider 7-zip, which has a command line option:

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

However, if this is critical data, you ought to consider using a commercial product with a support option.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (12/14/2007)
C'mon folks... WinZip Pro costs what, 60$??? Don't screw around with your archive data... do it right. You probably spend more than $60 a month buying coffee, softdrinks, and/or snacks.

Pro version is $49.95, with the added command-line support options.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

oh...and buy the 64-bit edition to get past the 65,536 files or 4GB/file limit.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?