Scheduled .zip in Win2k?

January 9, 2006 2:48 PM

I want to start doing nightly offsite copies of a MSSQL database at work. The .bak and .trn files made by the SQL Server Maintenance Plan add up to about 1 gig, but they compress to just over 100 megs, so I 'd much rather export a compressed version. What's the best Windows 2000 app where I can schedule the idea "zip the 2 newest files in this source directory into another directory, into a .zip with the filename based on the date"? If it can keep the 5 most recent .zip files only, all the better.....
posted by Steve3 to Computers & Internet (11 answers total)

This sounds like a perfect job for WSH and the Windows Task Scheduler. You might have to add the zip command line tools into the mix.
posted by majick at 2:53 PM on January 9, 2006

I use 7-zip for our backup compression. And I just use a simple batch file with a basic rotation:

del backup5.7z
rename backup4.7z backup 5.7z
rename backup3.7z backup 4.7z
rename backup2.7z backup 3.7z
rename backup.7z backup 2.7z
7zip [options] [files] backup.7z

Making the name based on the date is only useful for avoiding collisions if you're going to be storing heaps of old archives together. Remember, the file is automatically date stamped.

I don't have to worry about that "zip the 2 newest files in this source directory" bit as I have a specific SQL task that exports the database to the same file every night.
posted by krisjohn at 3:09 PM on January 9, 2006

handybackup will do just this. Backup over ftp or network with any number of newest backups.
However, Handy Backup does not run as a service, so you'll have to run this in a logged in terminal window.

If you're willing to spend a small amount of money, then use connected. Fully managed delta offsite backup with automatic compression, open file snapshotting, encryption and the ability to restore backups from the last {n} days.

I use both these solutions and they work great. HandyBackup is especially useful.
posted by seanyboy at 4:30 PM on January 9, 2006

Just noticed - there's also a pro version of handybackup that runs as a service.
posted by seanyboy at 4:36 PM on January 9, 2006

Use 7zip and script it. There is a windows service like cron that will work happily and you can just run a python/perl/batch file or whatever you are comfortable with nightly. You could also set that up to run ftp to send it someplace else.
posted by sien at 7:33 PM on January 9, 2006

The Windows Scheduler is not very robust, and is not recommended for mission critical or Enterprise endeavors. There's no real ability to log, to track logical decisions ("I did not start your job at 10:00 PM because you said not to start the job if the old job was still runnning", etc), errors, etc.

Against my recommendations, one of my teams used it schedule various maintenance jobs for our web service and DB, and we ended up writing a lot more "check to see if the job ran" code than was wise.
posted by Dunwitty at 7:52 PM on January 9, 2006

Also, Steve3 - Ask Metafilter is only as useful as you make it. Please check back from time to time and see if there are any questions you can answer.
posted by seanyboy at 12:02 AM on January 10, 2006

I do something similar using forfiles.exe to remove old backup files from disk. I've created a batch file and use Windows Scheduler to run it regularly.

Google for "forfiles.exe" for more info on where you can get it. It's part of the Win2k expansion pack. The syntax is simple enough. Here's and example from my .bat:

forfiles -p%%path%% -d-7 -c"CMD /c del @FILE"

-p specifies the path
-d tell it to work in days
-7 (minus seven) tells it seven days old
-c is to run a command

In quotes you would put your command to zip your files. @FILE tells it to do this to every file in the path.
posted by kc0dxh at 8:36 AM on January 10, 2006

Here's the batch file I wrote for work, to do something similiar, even generates a new zip file name based on the current date. This uses PKZip command line version, modify pkzip settings as neccessary, add to the windows scheduler using "at", works fine for me:

@echo off
@REM parses month, day, and year into mm , dd, yyyy formats

FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B


pkzipc -add -move -recurse -older=29 -newer=60 -path=root %mm%%dd%%yyyy%.zip *
posted by KirTakat at 9:58 AM on January 10, 2006

RAR has built in support for versioning:
e.g.
rar.exe a backup.rar -r -u -ver3 -dh -ms -os -ow mydatabase*.*
posted by Sharcho at 10:06 AM on January 10, 2006

Response by poster: Thanks, all. I'd say the forfiles + pkzip command line looks the most sensible to me. Although I've never done it, I should be able to write a forfiles that finds the most recent files and passes them to winzip, and then a second one to trim the archive folder to keep it from infinitely inflating.
posted by Steve3 at 12:07 PM on January 10, 2006