Database Backup Script for windows
In one of the project I am working on, the application needs to be deployed on windows server. To take the database backup, I wrote the script which does the following :
- Takes database dump and copy it to the backup folder.
- Zip that backup file and rename it to the format “YYYYMMDD_HHMMSS”
- Removes all the backups older than 30 days.
I used mysqldump command to take database backup. To zip the backup file using the command line, I found 7-zip (download) which is freely available. We need to have forfiles.exe (download) which enables us to remove old backup files (say older than 30 days).
[bash]
@echo off
CLS
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
set year=%date:~-4%
set month=%date:~3,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
set datetimef=%year%%month%%day%_%hour%%min%%secs%
"MYSQL_BIN_PATH\mysqldump" –user=root –password=MY_PASSWORD MY_DATABASE_NAME > "BACKUP_FOLDER_PATH\backup.sql"
"7ZIP_EXE_PATH\7z" a -tzip "BACKUP_FOLDER_PATH\"%datetimef%".zip" "BACKUP_FOLDER_PATH\backup.sql"
del "BACKUP_FOLDER_PATH\backup.sql"
"FORFILES_EXE_PATH\forfiles.exe" /p BACKUP_FOLDER_PATH /s /m *.* /d -30 /c "cmd /c del /q @path
[/bash]
Prerequisites for the above script to work would be :-
- Mysql and 7zip installed.
- Forfiles.exe is downloaded
- All the Path’s in script are replaced correctly.
I also used a background job to run this script automatically every hour on the working days. Though same can be achieved with windows scheduler too.
Hope this helped.
Cheers!
~~Amit Jain~~
amit@intelligrape.com
http://intelligrape.com/
You can do all these date/time variables with just one command (a bit harder to remember, though) :
for /F “tokens=1,2,3,4,5,6 delims=/: ” %%a in (‘echo %DATE: =0% %TIME: =0%’) do set CURDATE=%%c-%%b-%%a_%%d%%e%%f
You can choose whatever format you want with the 6 variables.
If you need to pipe commands or escape characters (like &), use a ^ before.
The “: =0” in the echo command is to pad with leading zeros for days/hours < 10.