Backup and Restore a MySQL Database in C#.NET



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


Comments

Author: adriancs24 Jun 2012 Member 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çe27 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: adriancs20 Jul 2012 Member Level: Bronze   Points : 0

Hi, Gokhan Gokce, can you tell me about the error message?