October 21, 2008 at 12:21 pm
Looking for some greatly appreciated script help.
I have 32 zip files in E:\SQL Backup\Data folder. The zip file name is ss-full-[dbname][backupdate].zip. Each zip file contains a database .bak file formatted as [dbname][backupdate].zip
For example: ss-full-DBSpoan20081021.zip contains DBSpoan10212008.bak
I need to unzip all 32 files into the same folder and then trim the date off the file name for restoration (i.e. DBSpoan10212008.bak becomes DBSpoan)
I was thinking of doing a cursor to unzip each file then trim the backup date off. This script was in kix but I want to house it within SQL using set variables and xp_cmdshell.
the path to the windows unzip is c:\progra~1\winzip\wzunzip
thanks
October 21, 2008 at 12:40 pm
Do all the bak files begin with seven letters then the date?
October 21, 2008 at 12:48 pm
No that would be too easy.... unfortunately 98% are 7 characters but two are not. One is 9 (DBSCOMMON) and one is six (RevRec)
I am working with this shell but not sure if querying from sys.databases to populate for db_name is the way to go...I have a tendency on scripts to over complicate!
This unzips but it doesn't trim...
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@fetch_status = 0
BEGIN
DECLARE @doscommand2 varchar(1000)
SET @doscommand2 = 'c:\progra~1\winzip\wzunzip -a -ex -P -r "Y:\HQGEAC2\Data\ss-full-'+@db_name+convert(varchar(10),getdate(),112)+'.zip" "Y:\HQGEAC\Data\'+@db_name+replace(convert(varchar(10),getdate(),110),'-','')+'.bak"'
EXEC master.dbo.xp_cmdshell @doscommand2
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
October 21, 2008 at 1:05 pm
Here is my final code I would like a second pair of eyes...
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@fetch_status = 0
BEGIN
DECLARE @doscommand2 varchar(1000)
SET @doscommand2 = 'c:\progra~1\winzip\wzunzip "E:\SQL Backup\Data\ss-full-'+@db_name+convert(varchar(10),getdate(),112)+'.zip" "E:\SQL Backup\Data\'+@db_name+'.bak"'
EXEC master.dbo.xp_cmdshell @doscommand2
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
October 21, 2008 at 1:14 pm
One more thing i cannot find it but does anyone know the parameter for unzip to not extract the results into their own folders?
October 22, 2008 at 11:15 am
Depends, the later versions needed an add-on for command line support.
Check the help topic "command line interface"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply