Database Backup Script for windows

14 / Dec / 2010 by Amit Jain 1 comments

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 :

  1. Takes database dump and copy it to the backup folder.
  2. Zip that backup file and rename it to the format “YYYYMMDD_HHMMSS”
  3. 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).

@echo off
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

Prerequisites for the above script to work would be :-

  1. Mysql and 7zip installed.
  2. Forfiles.exe is downloaded
  3. 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.

~~Amit Jain~~


comments (1 “Database Backup Script for windows”)

  1. M4rmott

    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.


Leave a Reply

Your email address will not be published. Required fields are marked *