.net备份mysql数据库备份_ASP.NET数据库备份和还原___偷的

先导入Interop.SQLDMO.dll

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

///

/// DBOperater 的摘要说明

///

public class DBOperater

{

public DBOperater()

{

//

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

//

}

//数据库备份

public static string DbBackup(string dbName,string backupDBName)

{

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

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

try

{

Object aip = ip;

oSQLServer.LoginSecure = false;

oSQLServer.Connect("127.0.0.1","sa","sa");

oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;

oBackup.Database = dbName;

oBackup.Files = @"d:\\aaa\\"+backupDBName+".bak";

oBackup.BackupSetName = backupDBName;

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

oBackup.Initialize = true;

oBackup.SQLBackup(oSQLServer);

return "数据库已成功经备份到["+oBackup.Files+"]";

}

catch(Exception ex)

{

throw new Exception("数据库备份失败: "+ex.Message);

}

finally

{

oSQLServer.DisConnect();

}

}

///

/// 数据库恢复

///

public static string  DbRestore(string dbName,string backupFile)

{

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

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

try

{

oSQLServer.LoginSecure = false;

oSQLServer.Connect("192.168.1.110", "new", "");

oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

oRestore.Database = dbName;

oRestore.Files = @"d:\\aaa\\"+backupFile;

oRestore.FileNumber = 1;

oRestore.ReplaceDatabase = true;

oRestore.SQLRestore(oSQLServer);

return "数据库"+dbName+"已经成功恢复!";

}

catch(Exception ex)

{

throw new Exception("数据库恢复失败: "+ex.Message );

}

finally

{

oSQLServer.DisConnect();

}

}

}存储过程

CREATE  PROCEDURE sp_KillThread  @dbname  varchar(20)

as

begin

declare  @sql  nvarchar(500),@temp varchar(1000)

declare  @spid  int

set  @sql='declare  getspid  cursor  for

select  spid  from  master..sysprocesses  where  dbid=db_id('''+@dbname+''')'

exec  (@sql)

open  getspid

fetch  next  from  getspid  into  @spid

while  @@fetch_status <> -1

begin

set @temp='kill  '+rtrim(@spid)

exec(@temp)

fetch  next  from  getspid  into  @spid

end

close  getspid

deallocate  getspid

end

GO

页面

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

//备份

protected void Button1_Click(object sender, EventArgs e)

{

try

{

//string path = this.File1.Value;//备份到...

string ret = DBOperater.DbBackup(t_db.Text, t_fileName.Text);

this.Label1.Text = ret;

}

catch (Exception ex)

{

this.Label1.Text = ex.Message;

}

}

//恢复

protected void Button2_Click(object sender, EventArgs e)

{

string path = "d:\\aaa\\";//this.File1.Value;//恢复文件路径

//杀死所有访问该数据库的进程

string conStr = "data source=localhost;database=master;user id=sa;password=password";

SqlConnection con = new SqlConnection(conStr);

string cmdText = String.Format("EXEC sp_KillThread @dbname='{0}'", t_db.Text);

SqlCommand com = new SqlCommand(cmdText, con);

try

{

con.Open();

com.ExecuteNonQuery();

con.Close();

//恢复数据库

string ret = DBOperater.DbRestore(t_db.Text, path);

this.Label1.Text = ret;

}

catch (Exception ex)

{

con.Close();

this.Label1.Text = ex.Message;

}

}

}