October 11, 2007 at 12:35 pm
Scenario is this..I need to regular refreshes of a TEST system which is close to 60 gigs of databases across the WAN...SO...I wanted to do the following steps.
1. ZIP all the backups I am going to be needing on (Server A Backup Server)
2. Move all the zips to a Local Drive on the TEST SQL server (Server B)
3. UNZIP all the backup files to the local drive
4. Run refresh script to refresh server.
I have #4 complete so now I am trying to figure out the best utility application for zipping and unzipping...
Other Information
The # of databases that need to be refreshed is dynamic so I would like to have something created that is dynamic as well...
I was hoping to find an application that I could call from a script something using the PSEXEC to execute a command line fuction over on the backup server to zip an entire directory...
Currently my boss uses gzip but that only seems to do individual files...
Any info is appreciated...
Thanks,
Leeland
October 11, 2007 at 12:51 pm
If you pay for a registered copy of WinZip you can get the command-line addon which would happily process multiple files. It is very inexpensive, but I believe there are free versions of other compression tools out there if you look for them.
Another suggestion would be to look into SQL LiteSpeed, SQL Backup, or one of the other SQL backup utilities that create compressed backups. This will cost you a little more (although some of them are very inexpensive), but you have the advantage of having all your backups run faster and take less disk space (roughly 75% faster & smaller depending on your data and compression level).
You could also use your one-file-at-a-time utility in a SSIS package with the For Each File loop to process all the files.
October 11, 2007 at 12:56 pm
Scott Coleman (10/11/2007)
If you pay for a registered copy of WinZip you can get the command-line addon which would happily process multiple files. It is very inexpensive, but I believe there are free versions out there if you look for them.Another suggestion would be to look into SQL LiteSpeed, SQL Backup, or one of the other SQL backup utilities that create compressed backups. This will cost you a little more (although some of them are very inexpensive), but you have the advantage of having all your backups run faster and take less disk space (roughly 75% faster & smaller depending on your data and compression level).
I WISH...we could utilize a form of third party application...however management is not conducive to change as well as others I work with. One of the reasons for this post is me attempting to streamline a process that had someone prior to me zipping all the files manually...copying them and then using the enterprise manager interface to restore over 40 databases manually through the GUI...an entire weekend project based on those steps...
I was hoping for something free or around there to utilize because I feel like I would have to pull teeth to get some sort of third party app in here to do something...if it costs money. Plus since this is only my responsibility no one else cares how long it takes 😀
October 11, 2007 at 12:59 pm
if you want to go the cheapie route, the last shareware/free version of PKZip 2.5 was all command line, and works fine as long as the files were under 2 gig in size. you can download a copy here:
http://www.stormrage.com/blogpix/PKZIP.zip
I wrote the proc below years ago, and it was doing a backup, zipping, and then moving to a mapped network drive. all you'd need to do is add the unzip portion, which is the same syntax anyway.
CREATE Procedure Proc_BackUpEverything
@LocalDrivevarchar(255) = 'D:\MSSQL7\BACKUP\', --max filename length is 255 anyway
@NetworkDrivevarchar(255) = 'Y:\'
As
Begin
declare@dbname as varchar(40),
@msgdb as varchar(255),
@dbbkpname as varchar(40),
@DY varchar(15),
@Pdy varchar(150),
@resultint
declarers_cursor CURSOR for select name from master.dbo.sysdatabases where name <> 'tempdb'
--filename validation
if SubString(@LocalDrive,len(@LocalDrive) - 1,1) <> '\'
Set @LocalDrive = @LocalDrive + '\'
if SubString(@NetworkDrive,len(@NetworkDrive) - 1,1) <> '\'
Set @NetworkDrive = @NetworkDrive + '\'
set @DY = (select datename(dw, getdate()) )
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
Print ' Beginning Full Backup Job'
WHILE @@FETCH_STATUS = 0
BEGIN
select @msgdb= 'database backup in progress: ' + @dbname + @DY
PRINT @msgdb
select @dbbkpname= @LocalDrive + @dbname +'-' + @DY + '.bak'
backup database @dbname to disk=@dbbkpname
--Create Zip File of the Backup
--pkzip -a "C:\MSSQL7\BACKUP\GMVB-Monday.zip" "C:\MSSQL7\BACKUP\GMVB-Monday.bak"
SET @Pdy=(select 'pkzip25 -add "' + @LocalDrive + @dbname +'-' + @DY + '.zip"' +' "' + @LocalDrive + @dbname +'-' + @DY + '.bak"')
--Execute command text held in @PDY
exec @result =xp_cmdshell @Pdy
IF (@result = 0)
PRINT 'Successfully Created Zip File'
ELSE
PRINT 'Failed to Create Zip File. Check Drive mappings and available harddrive space for both the Server and The remote location.'
--Create command line text based on results of @DY
SET @Pdy = (select 'COPY "' + @dbbkpname + '" "' + @NetworkDrive + @dbname +'-' + @DY + '.zip' + '"')
--Execute command text held in @PDY
exec @result = XP_CMDSHELL @Pdy
IF (@result = 0)
PRINT 'Successfully Copied Files to Network.'
ELSE
PRINT 'Failed to Copy to the network location. Check Drive mappings and available harddrive space for both the Server and The remote location.'
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
Print '======================'
Print '======================'
Print 'All Backup Jobs Completed'
End
Lowell
October 11, 2007 at 1:14 pm
WinZip Pro costs a whopping $50. (The Pro version is needed to use the command-line addon). It can create self-extracting files, so you only need to pay for one copy for the source server and unzip the files for free (legally) at the destination. If I faced spending weekends in the office doing this crap I would probably cough up the 50 bucks myself. (And start looking for another job). If you look hard on (legal) software download sites like tucows, you may find a freeware or shareware compression utility with command-line capability that you can use for free. If you want to risk your job over putting shareware on the company servers, that is.
Is there any way you can fire up multiple file copy jobs on the uncompressed files and completely saturate the WAN for a few days? That might encourage them to spend $50.
October 11, 2007 at 1:40 pm
I used to do something like this using gzip (http://www.gzip.org/). Free, easy to use, good compression and in my experience pretty fast.
October 11, 2007 at 3:06 pm
Scott Coleman (10/11/2007)
WinZip Pro costs a whopping $50. (The Pro version is needed to use the command-line addon). It can create self-extracting files, so you only need to pay for one copy for the source server and unzip the files for free (legally) at the destination. If I faced spending weekends in the office doing this crap I would probably cough up the 50 bucks myself. (And start looking for another job). If you look hard on (legal) software download sites like tucows, you may find a freeware or shareware compression utility with command-line capability that you can use for free. If you want to risk your job over putting shareware on the company servers, that is.Is there any way you can fire up multiple file copy jobs on the uncompressed files and completely saturate the WAN for a few days? That might encourage them to spend $50.
I like the idea but the end result would more than likely result in my getting Scolded 😀
I tried the gzip utility which I can figure out how to gzip an entire directory...however I can't figure out how to unzip and entire directory once it is moved I tried GUNZIP but I couldn't find any examples that just did a directory...you actually had to list all the files in the directory which would defeat the purpose...
Thanks for the responses guys I will see what I can find out about the winzip func...
October 11, 2007 at 3:16 pm
You should just be able to use a command like this:
gzip C:\mydir\*
Should zip everything up.
October 11, 2007 at 3:52 pm
Aaron Ingold (10/11/2007)
You should just be able to use a command like this:gzip C:\mydir\*
Should zip everything up.
I got that far...but what command would u use to unzip everything?
October 11, 2007 at 3:57 pm
Doh, my bad... misread your comment. To unzip you're going to use:
gzip -d c:\mydir\*
I'm sure there are probably easier ways to do it, but I've used gzip for so long that I'm of the mindset that if it ain't broke, don't fix it.
October 11, 2007 at 4:30 pm
In SQL 2005 you would have a much easier time attacking this problem with Integration Services. But if you're stuck with SQL 2000, here's one way.
1 Write a SQL script to get the names of the last full backup file for each database to be copied. This is available from the msdb tables backupset and backupmediafamily. Use the filenames to create the necessary zip and move commands. With WinZip it might look like the following script. Change the Winzip commands to the equivalent gzip commands, and you might think of a better work folder on your server than C:\Temp.
PRINT 'net use z: \\TestServer\share
path %PATH%;C:\Program Files\WinZip
BackupDriveLetter:
cd \backup_root'
DECLARE bakfiles CURSOR local fast_forward FOR
SELECT database_name, RIGHT(physical_device_name, CHARINDEX('\', REVERSE(physical_device_name))-1) AS filename
FROM msdb.dbo.backupmediafamily mf
INNER JOIN (
SELECT database_name, MAX(media_set_id) AS media_set_id
FROM msdb.dbo.backupset
WHERE type = 'D' and database_name not in ('master', 'msdb', 'model') and backup_start_date > dateadd(week, -1, getdate())
GROUP BY database_name
) s ON s.media_set_id = mf.media_set_id
OPEN bakfiles
DECLARE @dbname SYSNAME, @bakfile VARCHAR(260), @zipfile VARCHAR(260)
FETCH NEXT FROM bakfiles INTO @dbname, @bakfile
WHILE @@fetch_status = 0 BEGIN
SET @zipfile = 'C:\Temp\' + REPLACE(@bakfile, '.BAK', '.ZIP')
PRINT 'WZZIP -a "' + RTRIM(@dbname) + '\' + @bakfile + '" "' + @zipfile + '"'
PRINT 'MOVE "' + @zipfile + '" Z:\'
FETCH NEXT FROM bakfiles INTO @dbname, @bakfile
END
CLOSE bakfiles
DEALLOCATE bakfiles
PRINT 'net use Z: /DELETE'
GO
2 Create a BAT file to run the above script with osql and write the results to another BAT file, then run the BAT file. Run this batch file as a process step in DTS, or if the DTS package is not running on the server use a SQL step with "EXEC xp_cmdshell 'batfile1.bat"
osql -i scriptfile.sql -o batfile2.bat
batfile2.bat
3 I'll leave the unzip script up to you. A variation of the script above could be used to create the unzip batch file on the source server, you just have to move it to the test server and invoke it there.
October 11, 2007 at 4:31 pm
Aaron Ingold (10/11/2007)
Doh, my bad... misread your comment. To unzip you're going to use:gzip -d c:\mydir\*
I'm sure there are probably easier ways to do it, but I've used gzip for so long that I'm of the mindset that if it ain't broke, don't fix it.
Great man thanks worked like a charm!
October 12, 2007 at 9:55 am
Here is a solution for you
It is written in winbatch.
It is an exe file.
The .wbt file is the source code
The .pdf is a print out of the help file of the winbatch dll
the .bat file shows how to call the exe
I use it all the time for zipping bcp dump files prior to
FTP the file to the client
my contact info is in the .wbt file if you have questions.
Thor
October 12, 2007 at 5:14 pm
The most interesting part of this process to me is creating the script to restore all the databases after you've figured out how to zip, copy, and unzip the backup files. Since that was more interesting than anything else going on today, I had to play with it.
The attached script will create all the RESTORE commands for all the backup files that should have been copied to your test system. It includes WITH MOVE clauses in case the servers have different disk structures, and it will reference up to four backup files per RESTORE command in case you are doing striped backups.
This script would run on the source server to get the names of all the latest backup files, and the output it generates should be run on the test server after all backup files have been copied & unzipped.
October 13, 2007 at 10:14 am
another free commandline utility is 7-Zip
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply