asp.net 数据库备份恢复

ASP.NET数据库备份和还原

先导入Interop.SQLDMO.dll    （com组件）

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;  

        }   

   }