c#+mysql备份还原数据库_c# 备份与还原SQL/ACCESS 数据库的代码

//使用前须先引用SQLDMO.dll ADOX.dll JRO.dll 等组件，无须独立安装

//如：C:\Program Files\Common Files\System\ado\msadox.dll

//如：C:\Program Files\Common Files\System\ado\msjro.dll

//如：C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.DLL

using System;

using System.IO;

///

/// 数据库恢复和备份

///

public class DataBak

{

public DataBak()

{

//

// TO

//DO: 在此处添加构造函数逻辑

//

}

#region SQL数据库备份

///

/// SQL数据库备份

///

/// SQL服务器IP或(Localhost)

/// 数据库登录名

/// 数据库登录密码

/// 数据库名

/// 备份到的路径

public static bool BakSql(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)

{

SQLDMO.Backup oBackup = new SQLDMO.BackupClass();

SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();

try

{

oSQLServer.LoginSecure = false;

oSQLServer.Connect(ServerIP, LoginName, LoginPass);

oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;

oBackup.Database = DBName;

oBackup.Files = BackPath;

oBackup.BackupSetName = DBName;

oBackup.BackupSetDescription = "数据库备份";

oBackup.Initialize = true;

oBackup.SQLBackup(oSQLServer);

return true;

}

catch (Exception e)

{

return false;

throw new Exception(e.ToString());

}

finally

{

oSQLServer.DisConnect();

}

}

#endregion

#region SQL恢复数据库

///

/// SQL恢复数据库

///

/// SQL服务器IP或(Localhost)

/// 数据库登录名

/// 数据库登录密码

/// 要还原的数据库名

// 数据库备份的路径

public static bool RecoverSql(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)

{

SQLDMO.Restore orestore = new SQLDMO.RestoreClass();

SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();

try

{

//杀死所有SQL进程

SQLDMO.QueryResults qr = oSQLServer.EnumProcesses(-1);

int iColPIDNum = -1;

int iColDbName = -1;

for (int i = 1; i <= qr.Columns; i++)

{

string strName = qr.get_ColumnName(i);

if (strName.ToUpper().Trim() == "SPID")

{

iColPIDNum = i;

}

else if (strName.ToUpper().Trim() == "DBNAME")

{

iColDbName = i;

}

if (iColPIDNum != -1 && iColDbName != -1)

break;

}

for (int i = 1; i <= qr.Rows; i++)

{

int lPID = qr.GetColumnLong(i, iColPIDNum);

string strDBName = qr.GetColumnString(i, iColDbName);

if (strDBName.ToUpper() == DBName.ToUpper())

oSQLServer.KillProcess(lPID);

}

//执行还原工作

oSQLServer.LoginSecure = false;

oSQLServer.Connect(ServerIP, LoginName, LoginPass);

orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

orestore.Database = DBName;

orestore.Files = BackPath;

orestore.FileNumber = 1;

orestore.ReplaceDatabase = true;

orestore.SQLRestore(oSQLServer);

return true;

}

catch (Exception e)

{

return false;

throw new Exception(e.ToString());

}

finally

{

oSQLServer.DisConnect();

}

}

#endregion

#region 备份SQL数据库

///

/// 备份SQL数据库

///

/// 要备份的数据源名称

/// 备份到的数据库文件名称及路径

///

public static bool BakSql(string databaseName, string bakPath)

{

try

{

//声明临时数据库名称

string bakName = databaseName;

bakName += DateTime.Now.Year.ToString();

bakName += DateTime.Now.Month.ToString();

bakName += DateTime.Now.Day.ToString();

bakName += DateTime.Now.Hour.ToString();

bakName += DateTime.Now.Minute.ToString();

bakName += DateTime.Now.Second.ToString();

bakName += ".bak";

if (bakPath.LastIndexOf('\\') != bakPath.Length)

bakPath += '\\';

string sqlbak = "BACKUP DATABASE " + databaseName + " TO DISK = '" + bakPath + bakName + "' WITH INIT";

if (Common.ExeSql(sqlbak) != 0)

return true;

else

return false;

}

catch

{

return false;

}

}

#endregion

#region 还原SQL数据库

///

/// 还原指定的数据库文件

///

/// 要还原的数据库

/// 数据库备份文件及路径

///

public static bool RecoverSql(string databasename, string databasefile)

{

try

{

//还原指定的数据库文件

string sql = "RESTORE DATABASE " + databasename + " FROM DISK = '" + databasefile + "' ";

if (Common.ExeSql(sql) != 0)

return true;

else

return false;

}

catch

{

return false;

}

}

#endregion

#region 根据指定的文件名称创建Access数据库

///

/// 根据指定的文件名称创建数据

///

/// 绝对路径+文件名称

public static bool CreateAccess(string mdbPath)

{

if (File.Exists(mdbPath))//检查数据库是否已存在

{

return false;

}

else

{

try

{

mdbPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath;

//创建一个CatalogClass对象实例

ADOX.CatalogClass cat = new ADOX.CatalogClass();

//使用CatalogClass对象的Create方法创建ACCESS数据库

cat.Create(mdbPath);

return true;

}

catch

{

return false;

}

}

}

#endregion

#region 压缩Access数据库

///

/// 压缩Access数据库

///

/// 数据库绝对路径

public static bool CompactAccess(string mdbPath)

{

try

{

//声明临时数据库名称

string temp = DateTime.Now.Year.ToString();

temp += DateTime.Now.Month.ToString();

temp += DateTime.Now.Day.ToString();

temp += DateTime.Now.Hour.ToString();

temp += DateTime.Now.Minute.ToString();

temp += DateTime.Now.Second.ToString() + ".bak";

string srcconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath;

string destPath = mdbPath.Substring(0, mdbPath.LastIndexOf("\\") + 1) + temp;

string destconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destPath;

//创建一个JetEngineClass对象的实例

JRO.JetEngineClass jt = new JRO.JetEngineClass();

//使用JetEngineClass对象的CompactDatabase方法压缩修复数据库

jt.CompactDatabase(srcconn, destconn);

//拷贝临时数据库到目标数据库(覆盖)

File.Copy(destPath, mdbPath, true);

//最后删除临时数据库

File.Delete(destPath);

return true;

}

catch

{

return false;

}

}

#endregion

#region 备份Access数据库

///

/// 备份Access数据库

///

/// 要备份的数据库绝对路径

/// 备份到的数据库绝对路径

///

public static bool BakAccess(string srcPath, string aimPath)

{

try

{

//声明临时数据库名称

string temp = DateTime.Now.Year.ToString();

temp += DateTime.Now.Month.ToString();

temp += DateTime.Now.Day.ToString();

temp += DateTime.Now.Hour.ToString();

temp += DateTime.Now.Minute.ToString();

temp += DateTime.Now.Second.ToString() + ".bak";

string srcconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcPath;

string destconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aimPath.Substring(0, aimPath.LastIndexOf(".") + 1) + temp;

//创建一个JetEngineClass对象的实例

JRO.JetEngineClass jt = new JRO.JetEngineClass();

//使用JetEngineClass对象的CompactDatabase方法压缩修复数据库

jt.CompactDatabase(srcconn, destconn);

return true;

}

catch

{

return false;

}

}

#endregion

#region 还原Access数据库

///

/// 还原Access数据库

///

/// 备份的数据库绝对路径

/// 要还原的数据库绝对路径

public static bool RecoverAccess(string bakPath, string mdbPath)

{

try

{

File.Copy(bakPath, mdbPath, true);

return true;

}

catch

{

return false;

}

}

#endregion

}