How do you create a .bat file to run a MYSQLDUMP on all Databases?

Posted on 2011-04-21
Last Modified: 2012-05-11
I am trying to create a .bat file that will run the mysqldump.exe file and do a dump of the database to c:\mySqlDumps folder.  I can get this to work manually from the command prompt with the following:

mysqldump --all-databases  >  c:\mySqlDumps\mySQLBugDump.sql

However, I would like a .bat file for this as I plan on setting up a scheduled job to run the .bat file and have our backup software run a backup of the dump file.  The script I have in my .bat file is:

@echo off START C:\program files\mysql\mysql server 5.1\bin\mysqldump.exe --all-databases > C:\mysqldumps\mySQLBugDump.sql;

This produces the mySQLBugDump.sql file in the c:\mysqldumps directory but it is only 1KB in size (cpmpared to 30MB when I run the mysqldump command manually).

Any help resolving this would be greatly appreciated.  Thanks!
Question by:TigerBlood
    LVL 23

    Expert Comment

    - spaces between filename is not recognize in DOS (command prompt). you need to wrap it with double quotes "

    @echo off START "C:\program files\mysql\mysql server 5.1\bin\mysqldump.exe" --all-databases > C:\mysqldumps\mySQLBugDump.sql

    - if you already have the double quotes and its still error. remove the @echo off. run the BAT file manually from command prompt to see if any errors being prompt on the screen (don't double click the BAT from windows explorer).
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Please request that the MS SQL Server zone be removed as it does not apply to your question.

    Author Comment

    @ OP_Zaharin

    I ran the command with the double quotes from the command prompt and it also produced the file but only 1KB in size.

    I then removed the @echo OFF and it gave an error:

    "The System cannot find the file --all-databases".  I have pasted a screenshot below of the error.

    Error message
    This error is strange because when I am in the bin directory of my MySQL installation and run the command //// mysqldump --all-databases  >  c:\mySqlDumps\mySQLBugDump.sql //// from the command prompt it produces the a 30MB file containing all my database info.  There has to be a way for the.BAT file to run and produce the 30MB file with all the database info too.

    @ acperkins - Sorry, I will remove from MS Sql zone
    LVL 23

    Accepted Solution

    take out the START and try again.

    Author Comment

    @ OP_Zaharin - awesome... it is working.  Huge thanks!
    LVL 23

    Expert Comment

    great! no problem tiger :)

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now