Resources » Code Snippets » Automation

Backup and Restore a MySQL Database in C#.NET


Posted Date: 10-Jul-2008  Last Updated:   Category: Automation    
Author: Member Level: Silver    Points: 10





The code sample automates the process of backing up and restoring a MySQL database.

Create two batch files separately for backup(MySql_Backup.bat) and restore(MySql_Restore.bat).

MySql_Backup.bat



@ECHO off
cls
set DBchoice=%1%
set User=%2%
set Password=%3%
set pathchoice=%4%

@REM Remove double quotes from the path
@REM SET pathchoice=%pathchoice:"=%
@REM SET pathchoice=%pathchoice:"=%

mysqldump --add-drop-table -B %DBchoice% -u %User% --password=%Password% > %pathchoice%


MySql_Restore.bat




@ECHO off
cls

set User=%1%
set Password=%2%
set DBchoice=%3%
set pathchoice=%4%
set hostIP=%5%
set toolPath=%6%

@REM Remove double quotes from the path
@REM SET pathchoice=%pathchoice:"=%
@REM SET pathchoice=%pathchoice:"=%

%toolPath%\mysql -u %User% -h%hostIP% --password=%Password% %DBchoice% < %pathchoice%



Create the following method to execute the batch files with proper parameters


/// <summary&RT;
/// Author : Himasagar Kutikuppala
///A utility method that runs the batch file with supplied arguments.
/// </summary&RT;
/// <param name="batchFileName"&RT;Name of the batch file that should be run</param&RT;
/// <param name="argumentsToBatchFile"&RT;Arguments to the batch file</param&RT;
/// <returns&RT;Status of running the batch file</returns&RT;
protected bool ExecuteBatchFile(string batchFileName, string[] argumentsToBatchFile)
{
string argumentsString = string.Empty;
try
{
//Add up all arguments as string with space separator between the arguments
if (argumentsToBatchFile != null)
{
for (int count = 0; count < argumentsToBatchFile.Length; count++)
{
argumentsString += " ";
argumentsString += argumentsToBatchFile[count];
//argumentsString += "\"";
}
}

//Create process start information
System.Diagnostics.ProcessStartInfo DBProcessStartInfo = new System.Diagnostics.ProcessStartInfo(batchFileName, argumentsString);

//Redirect the output to standard window
DBProcessStartInfo.RedirectStandardOutput = true;

//The output display window need not be falshed onto the front.
DBProcessStartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
DBProcessStartInfo.UseShellExecute = false;

//Create the process and run it
System.Diagnostics.Process dbProcess;
dbProcess = System.Diagnostics.Process.Start(DBProcessStartInfo);

//Catch the output text from the console so that if error happens, the output text can be logged.
System.IO.StreamReader standardOutput = dbProcess.StandardOutput;

/* Wait as long as the DB Backup or Restore or Repair is going on.
Ping once in every 2 seconds to check whether process is completed. */
while (!dbProcess.HasExited)
dbProcess.WaitForExit(2000);

if (dbProcess.HasExited)
{
string consoleOutputText = standardOutput.ReadToEnd();
//TODO - log consoleOutputText to the log file.

}

return true;
}
// Catch the SQL exception and throw the customized exception made out of that
catch (SqlException ex)
{

ExceptionManager.Publish(ex);
throw SQLExceptionClassHelper.GetCustomMsSqlException(ex.Number);
}
// Catch all general exceptions
catch (Exception ex)
{
ExceptionManager.Publish(ex);
throw new CustomizedException(ARCExceptionManager.ErrorCodeConstants.generalError, ex.Message);
}
}



Regards
Himasagar Kutikuppala


Did you like this resource? Share it with your friends and show your love!

Responses to "Backup and Restore a MySQL Database in C#.NET"
Author: adriancs    24 Jun 2012Member Level: Bronze   Points : 0
Hi, there is an alternative to do this. Try this:

http://www.codeproject.com/Articles/256466/Making-Your-Own-MySQL-Backup-and-Restore-Tools-in



Guest Author: Gökhan Gökçe     27 Jun 2012
This is really good example. I tried your link adriancs, but it gave me error. I think the problem is about the size of sql file. I tried to change but nothing work. So this solution is better for me.


Author: adriancs    20 Jul 2012Member Level: Bronze   Points : 0
Hi, Gokhan Gokce, can you tell me about the error message?


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Sajib Barua
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India