Backup and Restore mysql database with windows batch file

10

One of the most common requirements for any project is backup and restore database which which can be done with SQL Data Recovery or manually if you have the required skills. As most of the user 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 scripts has been tested for windows platform only if you are looking for something else then these is not what you are looking for.

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.

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.

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 absolute path, just keep the database file in the same folder where your batch file exists.

Download
Share.
 

About Author

Entrepreneur & Dreamer

I am a passionate Software Professional, love to learn and share my knowledge with others.

Software is the hardware of my life.

10 Comments

  1. 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

  2. @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”

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

Leave A Reply

CommentLuv badge

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.