0

I am attempting to have a batch file run every night at midnight (via task scheduler) so that a backup of my mysql db is generated (using mysqldump), encrypted (using 7-zip), and moved to the appropriate folder. I modified a version of the code found at: https://sqlbackupandftp.com/blog/how-to-automate-mysql-database-backups-in-windows. The following is my modified version:

rem credentials to connect to mysql server
set mysql_user=***********
set mysql_password=***********
rem archive password
set encryption_password=***********
rem backup storage period (in days)
set max_keep_days=7
rem path to backup compression utility
set seven_zip_path=C:\Program Files\7-Zip\
rem backup file name generation
set backup_name=C:\Users\**********\Documents\Temp\all-databases-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.%
rem backup creation
mysqldump --user %mysql_user% --password=%mysql_password% --all-databases >> %backup_name%.sql
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D "Backup failed: error during dump creation" && exit
rem backup compression
"%seven_zip_path%7z" a -p%encryption_password% %backup_name%.zip %backup_name%.sql
if %ERRORLEVEL% neq 0 eventcreate /ID 1 /L APPLICATION /T ERROR /SO mysql-backup-script /D " Backup failed: error during archive creation" && exit
rem delete temporary .sql file
del %backup_name%.sql
rem copy backup to storage
robocopy C:\Users\**********\Documents\Temp\ C:\Users\**********\Desktop\Backups\ /e
rem local backup copy
del %backup_name%.zip
eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO mysql-backup-script /D "Backup successful"

(I removed the network path sections, as I am first trying to focus on getting this to run locally before re-incorporating those sections. Also, the backup_name variable didn't work until I used a full path)

This code does properly run when I run this myself, while logged in, etc. However, if I lock the pc, as I did last night, the file ran but failed once it reached the mysqldump line.

rygerx
  • 47
  • 7
  • Please replace all `if %ERRORLEVEL% neq 0` by `if ERRORLEVEL 1` which is the recommended syntax to check for exit code __GREATER OR EQUAL 1__ (in other words not successful) as described by the usage help of command __IF__ output on running `if /?` in a command prompt window. – Mofi Mar 01 '22 at 13:54
  • 1
    Then read the answers on [Time is set incorrectly after midnight](https://stackoverflow.com/questions/60124351/) and [Why does %date% produce a different result in batch file executed as scheduled task?](https://stackoverflow.com/questions/44664708/) The format of `%DATE%` and `%TIME%` is most likely different to format of date/time on running the batch file with your account as the environment variables are expanded with date/time in a format depending on configured region/country of the used account. Better is using a region/country independent solution like the `robocopy` solution. – Mofi Mar 01 '22 at 13:59
  • Thank you very much! I had the batch file pause after it errored, locked and tried again. The file had a blank due to the time (which would not be there at 10pm at night). Where this is running once per day, I will simply take the time portion out entirely. – rygerx Mar 01 '22 at 14:04
  • 1
    Read also my answer on [What must be taken into account on executing a batch file as scheduled task?](https://stackoverflow.com/a/41821620/3074564) Then read the general __issue__ chapters in [this answer](https://stackoverflow.com/a/60686543/3074564). Enclose all file/folder argument strings in `"`. It is never wrong doing that, but it can be wrong __not__ doing that. Be careful with __ROBOCOPY__ source and destination path. They should not end with a backslash. __ROBOCOPY__ is different to most Windows commands as it interprets ``\`` left to `"` as an escape character for the double quote. – Mofi Mar 01 '22 at 14:05
  • 1
    See also my answer on [Why is no string output with 'echo %var%' after using 'set var = text' on command line?](https://stackoverflow.com/a/26388460/3074564) It describes the recommended syntax `set "variable=string value"` for the definition of an environment variable and explains the reasons for using this syntax, especially on potentially critical strings like passwords. – Mofi Mar 01 '22 at 14:08
  • 1
    Finally on having your automated backup task working you might be interested in chapter __Best and simple backup deletion concept__ in my answer on [Bat file to delete files only when younger files are present](https://stackoverflow.com/a/47915658/3074564). Backups cannot be collected forever, older ones must be delete after some time. Taking that into account on backup creation, especially on creation of file name of backup file (date in international date format yyyy-MM-dd in file name) can make deletion of old backups no longer needed very simple. – Mofi Mar 01 '22 at 14:15
  • @Mofi I can't thank you enough, I will review all of these in depth. The deleting older backups is definitely something that I will need to eventually handle, so thanks again – rygerx Mar 01 '22 at 14:20

1 Answers1

1

The inclusion of %TIME% variable worked when the time was after 12pm, but after midnight and in the morning there suddenly was an extra space in the file name which caused an error.

(to those who are having similar issues, please see Mofi's replies as they will seriously help you out)

rygerx
  • 47
  • 7