熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

ASP.NET恢復備份Sql server

2013-11-13 10:04:25  來源: .NET編程 

  最近做的一個項目因為服務器是在特殊機房上的因為安全方面的考慮不能給我們開發者提供FTP服務所以每次更新版本都得自己跑一趟而他的機房有很遠所以我一直想能不能開發一個維護版本的系統呢對數據庫和代碼進行在線更新就不用自己跑了於是就有了下面的嘗試在線恢復和備份SQL Server

  前台代碼

  <%@ Page Language=C# AutoEventWireup=true CodeBehind=SqlDbMgmtaspxcs Inherits=SysSourceMgmtSqlDbMgmt %>

  <!DOCTYPE html PUBLIC //WC//DTD XHTML Transitional//EN <transitionaldtd>>

  <html xmlns=<;>

  <head runat=server>

  <title></title>

  </head>

  <body>

  <form id=form runat=server>

  <div>

  <table>

  <tr>

  <td >

  <span >操 作 數 據 庫</span>

  </td>

  <td>

  <asp:DropDownList ID=DropDownList runat=server FontSize=pt Width=px>

  </asp:DropDownList>

  <asp:TextBox ID=txtDbName runat=server></asp:TextBox>

  </td>

  <td >

  </td>

  </tr>

  <tr>

  <td >

  <span >備份名稱和位置</span>

  </td>

  <td >

  <asp:TextBox ID=TextBox runat=server FontSize=pt Width=px></asp:TextBox>

  </td>

  <td >

  <span >(如D:\beifen)</span>

  </td>

  </tr>

  <tr>

  <td colspan=>

  <asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=備份數據庫 />

  </td>

  </tr>

  </table>

  </div>

  <div >

  <table>

  <tr>

  <td >

  <span >操 作 數 據 庫</span>

  </td>

  <td>

  <asp:DropDownList ID=DropDownList runat=server FontSize=pt Width=px>

  </asp:DropDownList>

  </td>

  <td >

  </td>

  </tr>

  <tr>

  <td >

  <span >操 作 數 據 庫</span>

  </td>

  <td >

  <asp:FileUpload ID=FileUpload runat=server FontSize=pt Width=px />

  </td>

  <td >

  </td>

  </tr>

  <tr>

  <td colspan=>

  <asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=還原數據庫 />

  <asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=強制還原數據庫 />

  </td>

  </tr>

  </table>

  </div>

  </form>

  </body>

  </html>

  後台

  using System;

  using SystemCollectionsGeneric;

  using SystemLinq;

  using SystemWeb;

  using SystemWebUI;

  using SystemWebUIWebControls;

  using SystemDataSqlClient;

  using SystemIO;

  using SystemData;

  using SystemDiagnostics;

  namespace SysSourceMgmt

  {

  public partial class SqlDbMgmt : SystemWebUIPage

  {

  protected void Page_Load(object sender EventArgs e)

  {

  if (!IsPostBack)

  {

  try

  {

  string SqlStr = Server=(local);DataBase=master;Uid=sa;Pwd=;

  string SqlStr = Exec sp_helpdb;

  SqlConnection con = new SqlConnection(SqlStr);

  conOpen();

  SqlCommand com = new SqlCommand(SqlStr con);

  SqlDataReader dr = comExecuteReader();

  thisDropDownListDataSource = dr;

  thisDropDownListDataTextField = name;

  thisDropDownListDataBind();

  drClose();

  conClose();

  SqlStr = Server=(local);DataBase=master;Uid=sa;Pwd=;

  SqlStr = Exec sp_helpdb;

  con = new SqlConnection(SqlStr);

  conOpen();

  com = new SqlCommand(SqlStr con);

  dr = comExecuteReader();

  thisDropDownListDataSource = dr;

  thisDropDownListDataTextField = name;

  thisDropDownListDataBind();

  drClose();

  conClose();

  }

  catch (Exception)

  {

  }

  }

  }

  protected void Button_Click(object sender EventArgs e)

  {

  string dbName = stringEmpty;

  if (DropDownListItemsCount != )

  {

  dbName = DropDownListSelectedValueTrim();

  }

  else

  {

  dbName = txtDbNameTextTrim();

  }

  string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;

  string SqlStr = backup database + dbName + to disk= + thisTextBoxTextTrim() + bak;

  SqlConnection con = new SqlConnection(SqlStr);

  conOpen();

  try

  {

  if (FileExists(thisTextBoxTextTrim()))

  {

  ResponseWrite(<script language=javascript>alert(此文件已存在請從新輸入!);location=Defaultaspx</script>);

  return;

  }

  SqlCommand com = new SqlCommand(SqlStr con);

  comExecuteNonQuery();

  ResponseWrite(<script language=javascript>alert(備份數據成功!);</script>);

  }

  catch (Exception error)

  {

  ResponseWrite(errorMessage);

  ResponseWrite(<script language=javascript>alert(備份數據失敗!)</script>);

  }

  finally

  {

  conClose();

  }

  }

  protected void Button_Click(object sender EventArgs e)

  {

  string path = thisFileUploadPostedFileFileName; //獲得備份路徑及數據庫名稱

  string dbName = stringEmpty;

  if (DropDownListItemsCount != )

  {

  dbName = DropDownListSelectedValueTrim();

  }

  else

  {

  dbName = txtDbNameTextTrim();

  }

  string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;

  string SqlStr = @use master restore database + dbName + from disk= + path + ;

  SqlConnection con = new SqlConnection(SqlStr);

  conOpen();

  try

  {

  SqlCommand com = new SqlCommand(SqlStr con);

  comExecuteNonQuery();

  ResponseWrite(<script language=javascript>alert(還原數據成功!);</script>);

  }

  catch (Exception error)

  {

  ResponseWrite(errorMessage);

  ResponseWrite(<script language=javascript>alert(還原數據失敗!)</script>);

  txtDbNameText = SqlStr;

  }

  finally

  {

  conClose();

  }

  }

  /// <summary>

  /// 恢復數據庫可選擇是否可以強制還原(即在其他人在用的時候依然可以還原)

  /// </summary>

  /// <param name=databasename>待還原的數據庫名稱</param>

  /// <param name=databasefile>帶還原的備份文件的完全路徑</param>

  /// <param name=errormessage>恢復數據庫失敗的信息</param>

  /// <param name=forceRestore>是否強制還原(恢復)如果為TRUE則exec killspid 數據庫名 結束此數據庫的進程這樣才能還原數據庫</param>

  /// <returns></returns>

  public bool RestoreDataBase(string databasename string databasefile ref string returnMessage bool forceRestore SqlConnection conn)

  {

  bool success = true;

  string path = databasefile;

  string dbname = databasename;

  string restoreSql = use master;;

  if (forceRestore)//如果強制回復

  restoreSql += stringFormat(use master exec killspid {}; databasename);

  restoreSql += restore database @dbname from disk = @path;;

  SqlCommand myCommand = new SqlCommand(restoreSql conn);

  myCommandParametersAdd(@dbname SqlDbTypeChar);

  myCommandParameters[@dbname]Value = dbname;

  myCommandParametersAdd(@path SqlDbTypeChar);

  myCommandParameters[@path]Value = path;

  ResponseWrite(restoreSql);

  try

  {

  myCommandConnectionOpen();

  myCommandExecuteNonQuery();

  returnMessage = 還原成功;

  }

  catch (Exception ex)

  {

  returnMessage = exMessage;

  success = false;

  }

  finally

  {

  myCommandConnectionClose();

  }

  return success;

  }

  protected void Button_Click(object sender EventArgs e)

  {

  string path = thisFileUploadPostedFileFileName; //獲得備份路徑及數據庫名稱

  string dbName = stringEmpty;

  if (DropDownListItemsCount != )

  {

  dbName = DropDownListSelectedValueTrim();

  }

  else

  {

  dbName = txtDbNameTextTrim();

  }

  string returnMessage = stringEmpty;

  string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;

  SqlConnection con = new SqlConnection(SqlStr);

  RestoreDataBase(txtDbNameText path ref returnMessage truecon);

  ResponseWrite(returnMessage);

  }

  }

  }


From:http://tw.wingwit.com/Article/program/net/201311/12473.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.