sfejc411
asked on
wzzip .bat job for database file
Hello.
- Someone setup a batch job to backup a database file but I need help changing it. See below as what it is.
- Computer setup is the .bat job is on an XP Pro machine which is where the bat job is stored and run and it will back it up to a xp home shared folder on the same workgroup network. And they are using microsoft scheduled tasks to run the .bat job.
- I don't know the path to the database file but i know they are running like a POS microsoft computer with mysql on the XP Pro machine.
I would like to have it:
- backup daily like 11:00pm
- have it create the zip file with a name for example Tuesdays it would create a Tuesday.zip. Next Tuesday it would override the previous Tuesday.zip with the new Tuesday.zip. And for Wendesday override last Wendesday and so on.
- have all these zip files backed up into mapped drive z:\Backup
See below for what the .bat file is currently setup for
erase Backup.bck
rename Backup.bck
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
Thank you.
- Someone setup a batch job to backup a database file but I need help changing it. See below as what it is.
- Computer setup is the .bat job is on an XP Pro machine which is where the bat job is stored and run and it will back it up to a xp home shared folder on the same workgroup network. And they are using microsoft scheduled tasks to run the .bat job.
- I don't know the path to the database file but i know they are running like a POS microsoft computer with mysql on the XP Pro machine.
I would like to have it:
- backup daily like 11:00pm
- have it create the zip file with a name for example Tuesdays it would create a Tuesday.zip. Next Tuesday it would override the previous Tuesday.zip with the new Tuesday.zip. And for Wendesday override last Wendesday and so on.
- have all these zip files backed up into mapped drive z:\Backup
See below for what the .bat file is currently setup for
erase Backup.bck
rename Backup.bck
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
Thank you.
ASKER
Maybe I am wrong but although I am not a dos person, I don't see anywhere in the solution that addresses my questions.
please elaborate.
please elaborate.
I'm not real clear on what the existing .bat file does
Is this the file:
******backup.bat
erase Backup.bck
rename Backup.bck
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
*****END
If so, do you mean a .bck file instead of a .zip file?
If the above is your existing batch file, and you meant a .bck file, then change like so:
*****NewBackup.bat
@echo off
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
::Get the day
:: This assumes a date format of "DDD DD/MM/YYYY"
:: If your date format is different, the next line needs to be changed
set Today=%Date:~0,3%
::Convert to full name, instead of three letters
if "%Today%" EQU "Sun" set Today=Sunday
if "%Today%" EQU "Mon" set Today=Monday
if "%Today%" EQU "Tue" set Today=Tuesday
if "%Today%" EQU "Wed" set Today=Wednesday
if "%Today%" EQU "Thu" set Today=Thursday
if "%Today%" EQU "Fri" set Today=Friday
if "%Today%" EQU "Sat" set Today=Saturday
::Copy file, overwriting an existing file
copy /Y C:\Backup\Backup.bck z:\backup
*****END
Is this the file:
******backup.bat
erase Backup.bck
rename Backup.bck
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
*****END
If so, do you mean a .bck file instead of a .zip file?
If the above is your existing batch file, and you meant a .bck file, then change like so:
*****NewBackup.bat
@echo off
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
::Get the day
:: This assumes a date format of "DDD DD/MM/YYYY"
:: If your date format is different, the next line needs to be changed
set Today=%Date:~0,3%
::Convert to full name, instead of three letters
if "%Today%" EQU "Sun" set Today=Sunday
if "%Today%" EQU "Mon" set Today=Monday
if "%Today%" EQU "Tue" set Today=Tuesday
if "%Today%" EQU "Wed" set Today=Wednesday
if "%Today%" EQU "Thu" set Today=Thursday
if "%Today%" EQU "Fri" set Today=Friday
if "%Today%" EQU "Sat" set Today=Saturday
::Copy file, overwriting an existing file
copy /Y C:\Backup\Backup.bck z:\backup
*****END
One more thing...
If this command
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
will not overwrite an existing file, then the existing file needs to be deleted first. Add this line before the osql line
del c:\backup\backup.bck
If this command
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
will not overwrite an existing file, then the existing file needs to be deleted first. Add this line before the osql line
del c:\backup\backup.bck
ASKER
thank you for the quick responses.
The person who wrote the script made the file backup into Backup.bck. Maybe I should use the term archive or backup I am not sure?
I woud like the database waladb2 to be archived to a shared network drive and the output would be into a zip file with the name of the day the .bat was run and inside the zip file would be the file named Backup.bck.
reason being the zip compresses the bck into 2/3 the size.
hopefully that answers your question?
The person who wrote the script made the file backup into Backup.bck. Maybe I should use the term archive or backup I am not sure?
I woud like the database waladb2 to be archived to a shared network drive and the output would be into a zip file with the name of the day the .bat was run and inside the zip file would be the file named Backup.bck.
reason being the zip compresses the bck into 2/3 the size.
hopefully that answers your question?
I think my response does address your question. I provides a quick and easy wat to rename your files and eventually overwrite them, based on your criteria. My example archives backup files for 30 days and then overwrites them with FILENAME.zip being the current backup and FILENAME01.zip being yesterday's backup. You would need to add your existing backup routine after the example and schedule it to run every day.
Here is an updated script. It will first delete the previous temp backup files, create a new backup, zip it, then copy it to z:\backup with the current days name. It will need to be added to task scheduler to run at 11:00pm.
*****NewBackup.bat
@echo off
::Delete any temporary backup files
del c:\backup\backup.bck 2>NUL
del c:\backup\backup.zip 2>NUL
::Get the day
:: This assumes a date format of "DDD DD/MM/YYYY"
:: If your date format is different, the next line needs to be changed
set Today=%Date:~0,3%
::Create backup file
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
::Zip backup file
wzzip c:\backup\backup.zip c:\backup\backup.bck
::Convert to full name, instead of three letters
if "%Today%" EQU "Sun" set Today=Sunday
if "%Today%" EQU "Mon" set Today=Monday
if "%Today%" EQU "Tue" set Today=Tuesday
if "%Today%" EQU "Wed" set Today=Wednesday
if "%Today%" EQU "Thu" set Today=Thursday
if "%Today%" EQU "Fri" set Today=Friday
if "%Today%" EQU "Sat" set Today=Saturday
::Copy file, overwriting an existing file
copy /Y C:\Backup\Backup.zip z:\backup\%Today%.zip
*****NewBackup.bat
@echo off
::Delete any temporary backup files
del c:\backup\backup.bck 2>NUL
del c:\backup\backup.zip 2>NUL
::Get the day
:: This assumes a date format of "DDD DD/MM/YYYY"
:: If your date format is different, the next line needs to be changed
set Today=%Date:~0,3%
::Create backup file
osql -U sa -P -S reg1 -Q "BACKUP DATABASE waladb2 TO DISK = 'C:\Backup\Backup.bck'"
::Zip backup file
wzzip c:\backup\backup.zip c:\backup\backup.bck
::Convert to full name, instead of three letters
if "%Today%" EQU "Sun" set Today=Sunday
if "%Today%" EQU "Mon" set Today=Monday
if "%Today%" EQU "Tue" set Today=Tuesday
if "%Today%" EQU "Wed" set Today=Wednesday
if "%Today%" EQU "Thu" set Today=Thursday
if "%Today%" EQU "Fri" set Today=Friday
if "%Today%" EQU "Sat" set Today=Saturday
::Copy file, overwriting an existing file
copy /Y C:\Backup\Backup.zip z:\backup\%Today%.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will know by tomorrow and post my results. thanks.
So I will setup the task scheduler to run the .bat file daily. It will backup the backup.bck into a zip named with date of when its run. So lets say I run it today, Wedesday 21st. On Wednesday 28th will the code you gave me Delete the Wednesday 21st Zip file and create a new Wednesday zip file with Wednesday 28th backup.bck? If not then this is what I will need it to do replace the last weeks zip file with this weeks.
thanks.
So I will setup the task scheduler to run the .bat file daily. It will backup the backup.bck into a zip named with date of when its run. So lets say I run it today, Wedesday 21st. On Wednesday 28th will the code you gave me Delete the Wednesday 21st Zip file and create a new Wednesday zip file with Wednesday 28th backup.bck? If not then this is what I will need it to do replace the last weeks zip file with this weeks.
thanks.
Yes, it will do this. Today, it will create a backup.bck file, put this into backup.zip, then copy backup.zip to Wednesday.zip
Next wednesday, it will create a backup.bck file, put this into backup.zip, then copy backup.zip to Wednesday.zip - overwriting the previous Wednesday.zip.
Next wednesday, it will create a backup.bck file, put this into backup.zip, then copy backup.zip to Wednesday.zip - overwriting the previous Wednesday.zip.
ASKER
awesome!
ASKER
Results. the code creates a backup.bck in the c:\backup folder but creating a zip command does not appear to be working.
I removed all the code you gave me and just tried the code below to isolate if a zip is created at all but it was not created.
::Zip backup file
wzzip c:\backup\backup.zip c:\backup\backup.bck
I removed all the code you gave me and just tried the code below to isolate if a zip is created at all but it was not created.
::Zip backup file
wzzip c:\backup\backup.zip c:\backup\backup.bck
do you have wzzip installed? How are you creating your zip files?
Do you have wzzip in the path? If not, where is it installed?
You may need the full path to wzzip.exe. Try this (use your path to wzzip if different):
"C:\Program Files\WinZip\wzzip.exe" c:\backup\backup.zip c:\backup\backup.bck
Do you have wzzip in the path? If not, where is it installed?
You may need the full path to wzzip.exe. Try this (use your path to wzzip if different):
"C:\Program Files\WinZip\wzzip.exe" c:\backup\backup.zip c:\backup\backup.bck
ASKER
it's located in C:\Program Files\WinZip\WINZIP32.EXE.
I tried just changing the code you sent me many iterartions but no luck. tried
wzzip C:\Program Files\WinZip\WINZIP32.EXE c:\backup\backup.zip c:\backup\backup.bck
and
"C:\Program Files\WinZip\WINZIP32.EXE"
and
wzzip "C:\Program Files\WinZip\WINZIP32.EXE"
I tried just changing the code you sent me many iterartions but no luck. tried
wzzip C:\Program Files\WinZip\WINZIP32.EXE c:\backup\backup.zip c:\backup\backup.bck
and
"C:\Program Files\WinZip\WINZIP32.EXE"
and
wzzip "C:\Program Files\WinZip\WINZIP32.EXE"
Is winzip32.exe a command line zip program? On my computer, this is the windows version. If you don't have the command line extensions, you'll need them. See here to download
http://www.winzip.com/downcl.htm
After downloading/installing, use this:
"C:\Program Files\WinZip\wzzip.exe" c:\backup\backup.zip c:\backup\backup.bck
http://www.winzip.com/downcl.htm
After downloading/installing, use this:
"C:\Program Files\WinZip\wzzip.exe" c:\backup\backup.zip c:\backup\backup.bck
ASKER
yes, it''s the windows version. great i will download and try it out.
ASKER
hi.
everything is working now.
But I found out the trial of winzip and the winzip command line is not automatic backup. it will open a dos window that you have to press any key to continue. Someone gave me pkzip and i guess i don't have to worry about it being trial software as it worked right away.
Question. Does creating the backup.bck file, then creting the backup.zip then creating the TodayFilename.zip possibly leave room for corruption of the bck file? For nowThe db file is 50mb that we are zipping.
Not sure if I need to post this as another question?
Is there an easy way have the zipped file with the bck unzip and put into a specific folder automatically? For example crete an icon on the desktop that once clicked it will take the bck file and go into a folder where there is the previous days bck file and overwrite it?
everything is working now.
But I found out the trial of winzip and the winzip command line is not automatic backup. it will open a dos window that you have to press any key to continue. Someone gave me pkzip and i guess i don't have to worry about it being trial software as it worked right away.
Question. Does creating the backup.bck file, then creting the backup.zip then creating the TodayFilename.zip possibly leave room for corruption of the bck file? For nowThe db file is 50mb that we are zipping.
Not sure if I need to post this as another question?
Is there an easy way have the zipped file with the bck unzip and put into a specific folder automatically? For example crete an icon on the desktop that once clicked it will take the bck file and go into a folder where there is the previous days bck file and overwrite it?
There is always a possibility of corruption. Is the backup.bck file is created correctly from the database, (assuming no hardware failures) it is unlikely the result will get corrupt. I don't have much experience with zipping 1 very large file though, but I haven't heard of any problems either. Copying the file from 1 computer to another isn't likely to cause corruption. You could use the /V switch (which does a copy/verify) like so:
copy /Y /V C:\Backup\Backup.zip z:\backup\%Today%.zip
If you find out (heard/read) that zipping might cause corruption, it wouldn't have to be zipped, it could just be copied. 50MB is not that big, and with 7 days, that is only 350MB. Very small by todays HD sizes.
Another script could be written to unzip and move files to a specific location. I'm not real clear on what you would want it to do though.
copy /Y /V C:\Backup\Backup.zip z:\backup\%Today%.zip
If you find out (heard/read) that zipping might cause corruption, it wouldn't have to be zipped, it could just be copied. 50MB is not that big, and with 7 days, that is only 350MB. Very small by todays HD sizes.
Another script could be written to unzip and move files to a specific location. I'm not real clear on what you would want it to do though.
ASKER
I did not find out zipping causes corruption. Just wanted to ask if you knew?
We are backing up our biz computer a folder called backup to an online backup solution. The user then goes to their home and downloads the zipped file you helped me create. That is why i wanted to zip up the 50mb file. Then needs to extract that bck file into a spefic folder in their computer. Granted they could easily open winzip and copy the file but i would like to automate this if possible.
hope that makes sense?
We are backing up our biz computer a folder called backup to an online backup solution. The user then goes to their home and downloads the zipped file you helped me create. That is why i wanted to zip up the 50mb file. Then needs to extract that bck file into a spefic folder in their computer. Granted they could easily open winzip and copy the file but i would like to automate this if possible.
hope that makes sense?
Using a batch file, the user would have to download 2 files (the .zip file, and the .bat file), save them to the same location, then run the batch file. I don't think this would be easier.
I know winzip has a self-extracing zip creator, but it isn't free, and I've never used it. I think what it would do is create a .exe that the user would run, and it would extract itself. Posting a question in the Apps section is probably the best place to get more info on that if you want to pursue it.
There may be other programs that do something similiar for free, but I am not aware of any. If you are looking for free, and are planning on asking another question, mention that - hopefully someone else knows of a program.
I know winzip has a self-extracing zip creator, but it isn't free, and I've never used it. I think what it would do is create a .exe that the user would run, and it would extract itself. Posting a question in the Apps section is probably the best place to get more info on that if you want to pursue it.
There may be other programs that do something similiar for free, but I am not aware of any. If you are looking for free, and are planning on asking another question, mention that - hopefully someone else knows of a program.
ASKER
so if i understand correctly there is no command that wold go into a bat file to copy a file from a zip and past it into a folder correct?
A batch file could be written that would take a zip file and expand it's contents to a specific folder. In order for this to work, the user would have to have a batch file and a zip file. Since the user is downloading the zip file, i figured the user would also have to download the batch file.
If you want a batch file to open a zip file, and expand it's contents, that is not difficult. The command is:
wzunzip -o Filename.zip c:\backup\
This will unzip Filename.zip and put it's contents to c:\backup\. The -o option will automatically overwrite any existing files.
If you want a batch file to open a zip file, and expand it's contents, that is not difficult. The command is:
wzunzip -o Filename.zip c:\backup\
This will unzip Filename.zip and put it's contents to c:\backup\. The -o option will automatically overwrite any existing files.
Copy FILENAME29.zip FILENAME30.zip
Copy FILENAME28.zip FILENAME29.zip
Copy FILENAME27.zip FILENAME28.zip
Copy FILENAME26.zip FILENAME27.zip
Copy FILENAME25.zip FILENAME26.zip
Copy FILENAME24.zip FILENAME25.zip
Copy FILENAME23.zip FILENAME24.zip
Copy FILENAME22.zip FILENAME23.zip
Copy FILENAME21.zip FILENAME22.zip
Copy FILENAME20.zip FILENAME21.zip
Copy FILENAME19.zip FILENAME20.zip
Copy FILENAME18.zip FILENAME19.zip
Copy FILENAME17.zip FILENAME18.zip
Copy FILENAME16.zip FILENAME17.zip
Copy FILENAME15.zip FILENAME16.zip
Copy FILENAME14.zip FILENAME15.zip
Copy FILENAME13.zip FILENAME14.zip
Copy FILENAME12.zip FILENAME13.zip
Copy FILENAME11.zip FILENAME12.zip
Copy FILENAME10.zip FILENAME11.zip
Copy FILENAME09.zip FILENAME10.zip
Copy FILENAME08.zip FILENAME09.zip
Copy FILENAME07.zip FILENAME08.zip
Copy FILENAME06.zip FILENAME07.zip
Copy FILENAME05.zip FILENAME06.zip
Copy FILENAME04.zip FILENAME05.zip
Copy FILENAME03.zip FILENAME04.zip
Copy FILENAME02.zip FILENAME03.zip
Copy FILENAME01.zip FILENAME02.zip
Copy FILENAME.zip FILENAME01.zip
Del FILENAME.zip