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