Schedule a daily backups of SQL server and import script

Posted on Categories Server ManagementTags , , ,

This simple command line (with the help of Task Scheduler) will backup your database on a daily basis.

CMD File

DBconfig.cmd

::
::-------------CONFIG
::
::Startup directory
set DIRECTORY="E:\mysql\bin"
::Client, password
set CONFIG="E:\mysql\config.cnf"
::mysql.exe
set MYSQL="E:\mysql\bin\mysql.exe"
::mysqldump.exe
set MYSQLDUMP="E:\mysql\bin\mysqldump.exe"
::Folder saving
set BACKUP="I:\"
::Ext Daily backup
::%Date:~4,2%  month
::%Date:~10,4% year
set EXT=%date:~7,2%
::Databases export
set DATABASE[0]=table_ex1
set DATABASE[1]=table_ex2
set DATABASE[2]=table_ex3
set DATABASE[3]=table_ex4
set DATABASE[4]=table_ex5
set DATABASE[5]=table_ex6
::
::-------------END
::

DIRECTORY= Set the directory of MySQL for the command line.

CONFIG= Path to config.cfg to set the option –defaults-extra-file (user & password)since newer version of MySQL doesn’t accept option –password anymore.

MYSQL= Path to mysql.exe (to import)

MYSQLDUMP= Path to mysql.exe (to export)

BACKUP= Path to the backup folder (For windows Server 2012 r2, I recommend to install OneDrive and to point a OneDrive folder for extra precaution)

EXT= Append file extension; the day which will create a new file everyday!

DATABASE[0]= 1st Database to export
DATABASE[0]= 2nd Database to export; as many as you need.

Save the file as “DBconfig.cmd”

config.cnf

[client]
user=root
password=mypass

Save the file as “config.cnf”

DBexport.cmd

@echo off
call "C:\DBconfig.cmd"
::Backup
set x=0
:SymLoop
if defined DATABASE[%x%] (
    start /wait "DB" cmd /k "f: & cd %DIRECTORY% & %MYSQLDUMP% --defaults-extra-file=%CONFIG% --force %%DATABASE[%x%]%% > %BACKUP%%EXT%-%%DATABASE[%x%]%%.sql && exit"
    set /a "x+=1"
    GOTO :SymLoop
)
::Restart MySQL -if crashed
for /F "tokens=3 delims=: " %%H in ('sc query "MySQL" ^| findstr "        STATE"') do (
  if /I "%%H" NEQ "RUNNING" (
   net start "MySQL"
  )
)
exit

Edit the line call "C:\DBconfig.cmd" to the correct path.

Save the file as “DBexport.cmd”

DBimport.cmd

@echo off
call DBconfig.cmd
:START
:DB
set /p DATABASE=Enter Database name:
IF [%DATABASE%]==[] GOTO :DB
:FILE
set /p FILENAME=Enter Database file.sql name to import:
IF [%FILENAME%]==[] GOTO :FILE
 echo %DATABASE%
::IMPORT
start /wait "mail" cmd /k "f: & cd %DIRECTORY% & %MYSQL% --defaults-extra-file=%CONFIG% --force %DATABASE% < %BACKUP%%FILENAME%.sql && exit"
::Restart MySQL -if crashed
for /F "tokens=3 delims=: " %%H in ('sc query "MySQL" ^| findstr "        STATE"') do (
  if /I "%%H" NEQ "RUNNING" (
   net start "MySQL"
  )
)
set "DATABASE="
set "FILENAME="
GOTO :START
:END
exit

Save the file as “DBimport.cmd”

Optional: –add-drop-table to the cmd to drop the table before importing.

start /wait "DB" cmd /k "f: & cd %DIRECTORY% & %MYSQL% --defaults-extra-file=%CONFIG% --add-drop-table --force %DATABASE% < %BACKUP%%FILENAME%.sql && exit"

Task Manager

  • Create a new task with “Run whether user is logged on or not”.
  • Set a New ‘Trigger’ with setting set to ‘daily‘ or ‘Weekly
  • Set ‘Action’ to ‘Start a program’ and browse to DBexport.cmd for the Program/script

Import Database

Run DBimport.cmd, it will prompt for the name of the database and the backup file name (without extension .sql).

 

Leave a Reply

Your email address will not be published.