Backup and Restore mysql database with windows batch file

by  
Share:
Backup and Restore mysql database with windows batch file

One of the most common requirements for any project is to backup and restore the database. As most of the users including me work on XAMPP, WAMPP (for local use) we rely much on PHPMyAdmin for these functions until one fine day where I had to make a backup of database which contain more than 17 lakhs records(INNODB) which has referential integrity too. I tried using PHPMyAdmin but it had its limitation and then windows batch file comes into play.

This script has been tested for windows platform only.

Steps to backup database with windows batch file.

Assuming that you have a database “mydatabasename“, create a batch file to dump the database structure with records.

  • Create a folder name “test” in ur C:\ drive (You can choose your path and folder as per ur choice).
  • Create a new file and name it “db.bat“(or any other file name as per your choice but should end with .bat).
  • In that file write down the script given below and save it.
  • Double click on the file to execute it.
  • A new sql file will be created in your given location.

@echo off 
e:\xampp\mysql\bin\mysqldump --user=root --password= --result-file="c:\test\db.sql" mydatabasename 
echo Done!
pause
exit

Things to keep in mind before executing the above script.
  • Make sure you give the right drive path for your mysql bin location.
  • Don’t give any space between –user=root
  • Similarly don’t give any space between password –password=[your password]
  • If you don’t have any password leave blank
  • Correctly give the path name for your dumped sql file and make sure the folder has write permission.
  • Replace “mydatabasename” with your database name.
  • If you don’t want the window to exit until u give any input use pause command or else remove it.
  • For more information refer the official manual.

Steps to restore database with windows batch file.

Now that you have your backup file which you have created earlier, You can now restore it with the script given below.

@echo off 
e:\xampp\mysql\bin\mysql --user=root --password= -Dmydatabasename -e "source db.sql;"
echo Done!
pause
exit

Make sure you follow the same naming standards that you did before. In this case, when you give “db.sql” you don’t have to give the absolute path, just keep the database file in the same folder where your batch file exists. Download

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Kweku on

Great work here Shahrukh. This file works perfectly fine and has benefited me a lot.
Thanks Man

Reply
Shahrukh Khan on

Thanks

Reply
api on

You can make a tutorial
using php and mysqli for generate backup of database with two button.
one button save the path for generate backup, another restores the database with the archive that is generated.

for create a new backup, is necesary take into account,the datetime. for use forever the new backup for the restore the database.

thanks for your tutorials. nice web site

Reply
David on

How about selecting the location of of database file to restore using JFileChooser

Reply
Shahrukh Khan on

jfilechooser is in java i guess.. comes in swing package.. this project is done with phpmyadmin

Reply
sunil on

Awesome . It helped me a lot.

Reply
Kannan Sekar on

@ECHO OFF

set USERNAME=root
set PASSWORD=
set TIMESTAMP=%DATE:~10,4%.%DATE:~4,2%.%DATE:~7,2%-%TIME:~0,2%.%TIME:~3,2%.%TIME:~6,2%
set BACKUPPATH=C:\bck\

if not exist %BACKUPPATH% md %BACKUPPATH%

start “” “C:\xampp\mysql\bin\mysqldump.exe” –user %USERNAME% –password=%PASSWORD% tiger –result-file=”%BACKUPPATH%dbbackup.%TIMESTAMP%.sql” –databases tiger

REM Change working directory to the location of the DB dump file.
C:
CD \bck\

REM Compress DB dump file into CAB file (use “EXPAND file.cab” to decompress).
MAKECAB “dbbackup.%TIMESTAMP%.sql” “dbbackup.%TIMESTAMP%.sql.cab”

Reply
maman on

nice tutorial..
If I want to run this batch from client computer to make database backup in server computer, what should it to be?

Reply
Shahrukh Khan on

the same process just given in the tutorial.

Reply
anroy on

how to backup and drop table . Help me please.

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.