先导入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;
/// <summary>
/// DBOperater 的摘要说明
/// </summary>
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();
}
}
}
页面
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;
}
}
}
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;
/// <summary>
/// DBOperater 的摘要说明
/// </summary>
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;
}
}
}