It works quite well, but I was just hoping that anyone who has experience with batch could take a look at it for potential problems. The only one I've identified has been the case of email sends for errors failing if there is a network problem to the smtp relay. The only solution I could think of though would be to put a bigger wrapper around bmail (or a separate script) and designate some backup relays.
Once this thing is for sure done, I can use it to do various other backups on in-house systems with a high level of confidence that it works. Do what you will with it, it should work quite well as a fairly robust and free backup solution for windows. Also, if you have suggestions of free tools that do all this with more confidence, please let me know (despite the fact I've already done the work on this).
Code: Select all
rem Kayako Backup Script v0.93 Donald Haase 2010-2011
rem
rem This backup script dumps our Kayako SupportSuite database, then zips up that dump and deletes the original.
rem If any step along the way fails, we get notified via an email, and an eventlog message is created.
rem It then copies over that dump, along with all new attachments in ./files/ to each backup location specified.
rem This depends on bmail.exe ( http://www.beyondlogic.org/consulting/cmdlinemail/cmdlinemail.htm ) and 7za.exe ( http://www.7-zip.org/download.html )
rem The script was designed for Windows 2008, and on Windows 2003 requires the Resource Kit for robocopy.
rem
rem TODO: Test if destination is valid, perform old copy culling, verify destination IPs
rem
rem Changes:
rem v1.00 future
rem Script has been reviewed and tested in production with no issues. Correctly backs up to multliple locations.
rem Script is able to correctly exist on backup server without problem.
rem v0.93 Jan 19-26 2011
rem Interim prior to v1.0. Many changes brought about by migration to multi-IP backup + attachments backup.
rem Changed file_path to __production_DBPath to match new name scheme
rem New ERRORLEVEL setup using temp variable. Prevents from accidentally overriding it by using bmail/eventcreate
rem Correct SetLocal/EndLocal instead of manually cleaning used vars.
rem Revised text of error messages
rem v0.92 Jan 19 2011
rem Added comments to deliniate sections: SETUP, DUMPING, ZIPPING, COPYING, TEARDOWN.
rem Added system to copy to multiple destinations. Script is now aware of machine it is on and no modification is
rem needed in the case that it is run off a backup machine.
rem v0.91 Nov 19 2010
rem Migrated to bmail from sendmail. Allows message bodies without text files, also is more robust.
rem Along with the movie to bmail is more informative email messages.
rem Cleaned up spacing.
rem v0.90 Nov 18 2010
rem Was able to determine script failure was due to hours being parsed incorrectly. Windows' %time% var adds a
rem leading whitespace instead of a zero for single digit hours (' 9' and '10' instead of '09' and '10'). This
rem caused the filename to be invalid.
rem Added var for 'backup IP'.
rem Correctly cleared file_path var on exit.
rem Replaced copy with robocopy. Will retry 5 times on error, then generate event log message and send email.
rem Added extra fallthrough for 7z process. Now checks both if original archiving failed and if the test failed.
rem Added eventlog entries to signal start and end of process.
rem Changed all eventlog entries to refer to id 86.
rem Changed exits to 'goto cleanup'. Prevents variables from not being cleared.
rem v0.84 Nov 17 2010
rem Experienced script failure and mailsend did not work. Found problem to be missing message bodies for emails.
rem Also added event log logging for failure. Fixed problem with ext command (should be exit).
rem v0.83 Oct 23 2010
rem Added TODO and Changes. Made document public. Implemented in production.
rem *****SETUP*****
rem This ensures that all our vars stay local, as well as setting up delayed expansion which allows for var manipulation in loops
SetLocal EnableDelayedExpansion
rem Sign in by making an eventlog entry to note that the backup process has begun.
eventcreate /l APPLICATION /so Kayako_Backup /t Information /id 86 /d "Script has begun."
rem Set variables for SMTP mail params
set mail_params=-s SMTP_IP -t DLIST_EMAIL -f Kayako-Backup@marsys.com -h
rem This is a batch 'array'. each set of {IP, DBPath, FilesPath, Name} defines a single site/location that can be used as a backup.
rem You should include the 'production' IP as well. This makes it easy to fail over by not requiring you to change the script.
set __backup_IP.1=10.2.0.107
set __backup_DBPath.1=c$\kayako_backup
set __backup_FilesPath.1=c$\xampp\htdocs\supportsuite\files
set __backup_Name.1=Production_Server
set __backup_IP.2=10.3.0.157
set __backup_DBPath.2=c$\kayako_backup
set __backup_FilesPath.2=c$\xampp\htdocs\supportsuite\files
set __backup_Name.2=Backup_Server
rem These are two additional backups commented out because connectivity has not yet been made to them.
rem set __backup_IP.3=10.6.11.1
rem set __backup_DBPath.3=e$\kayako_backup
rem set __backup_FilesPath.3=e$\kayako_backup\files
rem set __backup_Name.3=Removable_Backup
rem set __backup_IP.4=10.1.3.50
rem set __backup_DBPath.4=c$\kayako_backup
rem set __backup_FilesPath.4=c$\xampp\htdocs\supportsuite\files
rem set __backup_Name.4=Staging_Server
rem Set locations for files. Ideally these would be defaults for the above, and only if different would they be noted.
rem __productionIP=.... This is not required, but automatically determined
set __production_DBPath=c:\kayako_backup
set __production_FilesPath=c:\xampp\htdocs\supportsuite\files
rem This is a kludge required due to windows' poorly devised time stamp format. 'If there is a leading blank, use a 0 instead.'
if "%time:~-11,1%"==" " set file_name=kayako_suite%date:~-4,4%%date:~-10,2%%date:~-7,2%0%time:~-10,1%%time:~-8,2%
if not "%time:~-11,1%"==" " set file_name=kayako_suite%date:~-4,4%%date:~-10,2%%date:~-7,2%%time:~-11,2%%time:~-8,2%
rem *****DUMPING*****
rem Test that MySQL service is running
sc query "MySQL" | findstr "RUNNING"
set /A __tmperrlevel=%ERRORLEVEL%
if not %__tmperrlevel%==0 (
eventcreate /l APPLICATION /so Kayako_Backup /t ERROR /id 86 /d "Script failed: MySQL not running. Errorlevel: %__tmperrlevel%."
bmail %mail_params% -a "Kayako Backup Failure" -b "Could not dump database: MySQL service not started."
goto Cleanup
)
rem Perform the database dump
c:\xampp\mysql\bin\mysqldump -u root -pPASSWORD DB_TABLE > %__production_DBPath%\%file_name%.sql
rem If the database dump fails, send out an email and exit.
set /A __tmperrlevel=%ERRORLEVEL%
if not %__tmperrlevel%==0 (
eventcreate /l APPLICATION /so Kayako_Backup /t ERROR /id 86 /d "Script failed to dump database. Errorlevel: %__tmperrlevel%."
bmail %mail_params% -a "Kayako Backup Failure" -b "Could not dump database: error %__tmperrlevel%"
goto Cleanup
)
rem *****ZIPPING*****
rem Move to dir where the backups are (7za doesn't like absolute paths)
cd %__production_DBPath%
rem Zip up the database dump.
7za a -tzip -mx5 %file_name%.zip %file_name%.sql
set /A __tmperrlevel=%ERRORLEVEL%
if not %__tmperrlevel%==0 goto ziperror
rem For extra security test this newly created zip
7za t %file_name%.zip %file_name%.sql
rem If there was no error, delete kayako_suite.sql
set /A __tmperrlevel=%ERRORLEVEL%
if not %__tmperrlevel%==0 goto ziperror
del/s %file_name%.sql
set file_name=%file_name%.zip
goto noziperror
rem If there was an error
:ziperror
eventcreate /l APPLICATION /so Kayako_Backup /t Warning /id 86 /d "Script failed to zip database dump. Errorlevel: %__tmperrlevel%."
bmail %mail_params% -a "Kayako Backup Warning" -b "Failed to zip database dump (error: %__tmperrlevel%), continuing using uncompressed dump."
set file_name=%file_name%.sql
:noziperror
rem *****COPYING*****
rem Looping over target backup sites to copy over backup DB and 'files' folder from htdocs (which stores attachments). %%A is index, %%B is IP
for /f "tokens=2* delims=.=" %%A IN ('SET __backup_IP.') DO (
rem Run IP config and search for the IP of the current backup site. If it is found in ipconfig,
rem it is the site we are currently on and there is no need to copy anything.
ipconfig /all | findstr %%B
if not !ERRORLEVEL!==0 (
rem Start by building the path for the DB backup
set __backup_fullpath_TEMP=!__backup_DBPath.%%A!
set __backup_fullpath_TEMP=\\%%B\!__backup_fullpath_TEMP!\
echo Copying file to !__backup_fullpath_TEMP!
rem Copy over DB to backup site. 'Z' makes the network copy restartable, 'R' is the number of retries on connection error.
robocopy !__production_DBPath! !__backup_fullpath_TEMP! !file_name! /COPY:DATSO /Z /R:5
rem If there is an error, shout it out.
set /A __tmperrlevel=!ERRORLEVEL!
rem ERRORLEVEL 0: No errors, no files copied. 1: No errors, new file copied. 2,4,8: issues with mirrored files, no errors. 16: No files copied. Errors.
rem This checks for 1, as it should be copying one new file only, the DB backup.
if not !__tmperrlevel!==1 (
eventcreate /l APPLICATION /so Kayako_Backup /t Warning /id 86 /d "Script failed to copy database dump !filepath!/!file_name! to !__backup_fullpath_TEMP!. Errorlevel: !__tmperrlevel!."
bmail %mail_params% -a "Kayako Backup Warning" -b "Failed to copy database dump !filepath!/!file_name! to !__backup_fullpath_TEMP! with Errorlevel: !__tmperrlevel!. Please investigate connection."
) else (
eventcreate /l APPLICATION /so Kayako_Backup /t Information /id 86 /d "Script copied database dump !filepath!/!file_name! to !__backup_fullpath_TEMP!."
)
rem Copy 'files' folder over next.
set __backup_fullpath_TEMP=!__backup_FilesPath.%%A!
set __backup_fullpath_TEMP=\\%%B\!__backup_fullpath_TEMP!\
echo Copying file to !__backup_fullpath_TEMP!
rem Synchronize attachments to backup site. This will *not* delete old attachments. I am not certain of that is the correct behaviour.
rem The /NFL switch is so that it doesn't spend time printing a file list, which takes a considerable amount of time with 2000+ attachments
rem /NFL should be turned off when debugging is necessary.
robocopy !__production_FilesPath! !__backup_fullpath_TEMP! attach_* /COPY:DATSO /Z /NFL /R:5
rem If there is an error, shout it out.
set /A __tmperrlevel=!ERRORLEVEL!
rem greater than or equal to 8 means there was an actual error
if !__tmperrlevel! geq 4 (
eventcreate /l APPLICATION /so Kayako_Backup /t Warning /id 86 /d "Script failed to copy attachments to !__backup_fullpath_TEMP!. Errorlevel: !__tmperrlevel!."
bmail %mail_params% -a "Kayako Backup Warning" -b "Failed to copy attachments to !__backup_fullpath_TEMP! with Errorlevel: !__tmperrlevel!. Please investigate connection."
) else (
eventcreate /l APPLICATION /so Kayako_Backup /t Information /id 86 /d "Script copied attachments to !__backup_fullpath_TEMP!."
)
)
rem else This script is running on the currently chosen backup site. No need to copy anything.
)
rem *****TEARDOWN*****
:Cleanup
rem Clean up vars, return us to normal
EndLocal
cd /d %~dp0
rem Sign out by making an eventlog entry to note that the backup process has completed.
eventcreate /l APPLICATION /so Kayako_Backup /t Information /id 86 /d "Script has completed."
