ASP.NET数据库备份和还原___偷的

ASP.NET数据库备份和还原

<%@ page import="org.jfree.chart.JFreeChart"%>
<%@ page import="org.jfree.chart.ChartFactory"%>先导入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();
        }
    }
}

存储过程

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;
        }
    }
}