使用SQLDMO进行数据库备份与还原

bakServer.cs类

 

 

using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using SQLDMO; using System.Windows.Forms; using System.Collections;

namespace MeasurementWellCurve.BLL {     /// <summary>     /// 数据库的备份与还原     /// </summary>     public class bakServer     {         private string ServerName;       //数据服务器名称           private string UserName;           //用户名称           private string Password;           //用户密码         private string DBName;             //数据库名称         //数据库连接字符串         private string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();         ///   <summary>           ///   取得数据库服务器列表           ///   </summary>           ///   <returns>数据库服务器列表</returns>           public ArrayList GetServerList()         {             ArrayList alServers = new ArrayList();             SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();             try             {                 SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();                 for (int i = 1; i <= serverList.Count; i++)                 {                     alServers.Add(serverList.Item(i));                 }             }             catch (Exception e)             {                 throw(new   Exception("取数据库服务器列表出错："+e.Message))   ;                 }             finally             {                 sqlApp.Quit();             }             return alServers;         }        

        ///   <summary>           ///   取得指定数据库列表           ///   </summary>           ///   <param   name="strServerName">服务器名称</param>           ///   <param   name="strUserName">用户名称</param>           ///   <param   name="strPwd">用户密码</param>           ///   <returns>数据库列表</returns>           public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)         {             ServerName = strServerName;             UserName = strUserName;             Password = strPwd;             ArrayList alDbs = new ArrayList();             SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();             SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();             try             {                 svr.Connect(ServerName, UserName, Password);                 foreach (SQLDMO.Database db in svr.Databases)                 {                     if (db.Name != null)                         alDbs.Add(db.Name);                 }             }             catch (Exception err)             {                 throw (new Exception("连接数据库出错：" + err.Message));                 }             finally             {                 svr.DisConnect();                 sqlApp.Quit();             }             return alDbs;         }

        ///   <summary>           ///   数据库的备份         ///   </summary>           ///   <param   name="strDbName">数据库名称</param>           ///   <param   name="strFileName">备份文件名（完整路径）</param>            ///   <param   name="strPwd">密码</param>           ///   <returns>备份成功返回true   ，否则返回false</returns>           public bool BackUPDB(string strFileName)         {             SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();             try             {                 ServerName = GetServerName();                 UserName = GetUserID();                 Password = GetPassword();

                svr.Connect(ServerName, UserName, Password);                 SQLDMO.Backup bak = new SQLDMO.BackupClass();                 bak.Action = 0;                 //bak .Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;                 bak.Database = GetDBName();                 bak.Files = strFileName;                 bak.BackupSetDescription = "数据库备份";                 bak.Initialize = true;                 bak.SQLBackup(svr);                 return true;             }             catch (Exception err)             {                 throw (new Exception("备份数据库失败" + err.Message));                 return false;             }             finally             {                 svr.DisConnect();             }         }         ///   <summary>           ///   数据库的恢复和杀死进程           ///   </summary>           ///   <param   name="strFileName">备份文件名</param>            ///   <returns>恢复成功返回true   ，否则返回false</returns>           public bool RestoreDB(string strFileName)         {             SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();             try             {                 ServerName = GetServerName();                 UserName = GetUserID();                 Password = GetPassword();

                svr.Connect(ServerName, UserName, Password);                 SQLDMO.QueryResults qr = svr.EnumProcesses(-1);                 int iColPIDNum = -1;                 int iColDbName = -1;                 for (int i = 1; i <= qr.Columns; i++)                 {                     string strName = qr.get_ColumnName(i);                     if (strName.ToUpper().Trim() == "SPID")                     {                         iColPIDNum = i;                     }                     else if (strName.ToUpper().Trim() == "DBNAME")                     {                         iColDbName = i;                     }                     if (iColPIDNum != -1 && iColDbName != -1)                         break;                 }

                for (int i = 1; i <= qr.Rows; i++)                 {                     int lPID = qr.GetColumnLong(i, iColPIDNum);                     string strDBName = qr.GetColumnString(i, iColDbName);                     if (strDBName.ToUpper() == GetDBName().ToUpper())                         svr.KillProcess(lPID);                 }

                SQLDMO.Restore res = new SQLDMO.RestoreClass();                 res.Action = 0;                                 res.Files = strFileName;                 res.Database = GetDBName();                 res.ReplaceDatabase = true;                 res.SQLRestore(svr);                 return true;             }             catch (Exception err)             {                 throw(new   Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！"+err.Message))   ;                     return false;             }             finally             {                 svr.DisConnect();             }         }         /// <summary>         /// 获取服务器名称         /// </summary>         /// <returns></returns>         public string GetServerName()         {             string strServerName = "";             string[] connList = connString.Split(';');             for (int index = 0; index < connList.Length; index++)             {                 if (connList[index].Contains("Data Source"))                 {                     strServerName = connList[index].Substring(connList[index].IndexOf('=')+1);                     break;                 }             }             return strServerName;         }         /// <summary>         /// 获取数据库名称         /// </summary>         /// <returns></returns>         public string GetDBName()         {             string strDBName = "";             string[] connList = connString.Split(';');             for (int index = 0; index < connList.Length; index++)             {                 if (connList[index].Contains("Initial Catalog"))                 {                     strDBName = connList[index].Substring(connList[index].IndexOf('=') + 1);                     break;                 }             }             return strDBName;         }         /// <summary>         /// 得到连接数据库用户ID         /// </summary>         /// <returns></returns>         public string GetUserID()         {             string strUserID = "";             string[] connList = connString.Split(';');             for (int index = 0; index < connList.Length; index++)             {                 if (connList[index].Contains("User ID"))                 {                     strUserID = connList[index].Substring(connList[index].IndexOf('=') + 1);                     break;                 }             }             return strUserID;         }         /// <summary>         /// 得到连接数据库密码         /// </summary>         /// <returns></returns>         public string GetPassword()         {             string strPassword = "";             string[] connList = connString.Split(';');             for (int index = 0; index < connList.Length; index++)             {                 if (connList[index].Contains("Password"))                 {                     strPassword = connList[index].Substring(connList[index].IndexOf('=') + 1);                     break;                 }             }             return strPassword;         }

        /// <summary>         /// 取得备份文件名         /// </summary>         /// <returns></returns>         public string GetFileName()         {             string fileName = "";             string year = DateTime.Now.Year.ToString();             string month = DateTime.Now.Month.ToString();             if (Convert.ToInt32(month) < 10)             {                 month = "0" + month;             }             string day = DateTime.Now.Day.ToString();             if (Convert.ToInt32(day) < 10)             {                 day = "0" + day;             }             string hour = DateTime.Now.Hour.ToString();             if (Convert.ToInt32(hour) < 10)             {                 hour = "0" + hour;             }             string minute = DateTime.Now.Minute.ToString();             if (Convert.ToInt32(minute) < 10)             {                 minute = "0" + minute;             }             string secode = DateTime.Now.Second.ToString();             if (Convert.ToInt32(secode) < 10)             {                 secode = "0" + secode;             }             fileName = GetDBName() + year + month + day + hour + minute + secode + ".bak";

            return fileName;         }     } }

 

数据库备份

 <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">     <title>数据库备份</title>     <link rel="Stylesheet" href="../CSS/style.css" />     <script type="text/javascript" src="../JS/common.js"></script>     <script type="text/javascript" src="../JS/ajax.js"></script> </head> <body>     <form id="form1" runat="server">     <div>         <table cellSpacing="0" cellPadding="0" width="100%" bgColor="#c4d8ed" border="0">           <tbody>             <tr>               <td style="height: 27px"><IMG alt="" src="../images/r_1.gif"></td>               <td width="100%" background="../images/r_0.gif" style="height: 5px"></td>               <td style="height: 27px"><IMG alt="" src="../images/r_2.gif"></td>             </tr>             <tr>               <td ></td>               <td>               <table class="toptable grid" cellspacing="1" cellpadding="1" align="center" border="1">                 <tbody>                   <tr>                     <td class="category" colspan="3">数据库备份</td>                   </tr>                   <tr>                     <td colspan="3" class="tdControl" style="height:24px; border:0;">                         <div id="divMsg" runat="server" style="color:Red;"></div>                     </td>                   </tr>                   <tr>                     <td colspan="3" class="tdControl">                         请指定用于备份的目录：(如未指定位置，默认备份至 软件目录/DBBackUp/ )                     </td>                   </tr>                   <tr>                     <td class="tdControl" style="text-align:right;">备份目录：</td>                     <td colspan="2" class="tdControl">                         <asp:Panel ID="Panel1" runat="server">                             <asp:TextBox ID="txtPath" runat="server" Width="297px"></asp:TextBox>                             &nbsp;&nbsp;<span style="color:Red;">例：C:/DBBack/</span>                         </asp:Panel>                     </td>                   </tr>                   <tr>                     <td colspan="3" style="padding-left:400px;" class="tdControl">                         <asp:Button ID="btnBackUp" runat="server" Text="开始备份"  CssClass="btn"                             οnclick="btnBackUp_Click" />                      </td>                   </tr>                 </tbody>               </table>              </td>             <td ></td>           </tr>           <tr>             <td ><IMG alt="" src="../images/r_4.gif"></td>             <td></td>             <td><IMG alt="" src="../images/r_3.gif"></td>           </tr>            </tbody>         </table>     </div>     </form> </body> </html>

 

/// <summary>         /// 数据库备份         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>         protected void btnBackUp_Click(object sender, EventArgs e)         {             bakServer bak;             this.divMsg.InnerHtml = "数据正在备份中，请稍后……";             this.btnBackUp.Enabled = false;             try             {                 bak = new bakServer();                 if (this.txtPath.Text == "")                 {                     string fileName = Server.MapPath("/DBBackUp/") + bak.GetFileName();                     if (bak.BackUPDB(fileName))                     {                         this.divMsg.InnerHtml = "数据库备份成功。";                         this.btnBackUp.Enabled = true;                     }                     else                     {                         this.divMsg.InnerHtml = "数据库备份失败。";                         this.btnBackUp.Enabled = true;                     }                 }                 else                 {                     if(txtPath.Text.Trim().Substring(txtPath.Text.Trim().Length-1)!=@"/")                     {                         this.txtPath.Text = this.txtPath.Text.Trim() + @"/";                     }                     string fileName = this.txtPath.Text.Trim() + bak.GetFileName();                     if (bak.BackUPDB(fileName))                     {                         this.divMsg.InnerHtml = "数据库备份成功。";                         this.btnBackUp.Enabled = true;                     }                     else                     {                         this.divMsg.InnerHtml = "数据库备份失败。";                         this.btnBackUp.Enabled = true;                     }                 }             }             catch (Exception ex)             {                 Session["errorMsg"] = ex.Message;                 Response.Redirect("Error.aspx", false);             }         }

 

数据库还原

<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">     <title>数据库还原</title> <link rel="Stylesheet" href="../CSS/style.css" />     <script type="text/javascript" src="../JS/common.js"></script>     <script type="text/javascript" src="../JS/ajax.js"></script> </head> <body>     <form id="form1" runat="server">     <div>         <table cellSpacing="0" cellPadding="0" width="100%" bgColor="#c4d8ed" border="0">           <tbody>             <tr>               <td style="height: 27px"><IMG alt="" src="../images/r_1.gif"></td>               <td width="100%" background="../images/r_0.gif" style="height: 5px"></td>               <td style="height: 27px"><IMG alt="" src="../images/r_2.gif"></td>             </tr>             <tr>               <td ></td>               <td>               <table class="toptable grid" cellspacing="1" cellpadding="1" align="center" border="1">                 <tbody>                   <tr>                     <td class="category" colspan="3">数据库还原</td>                   </tr>                   <tr>                     <td colspan="3" class="tdControl" style="height:24px; border:0;">                         <div id="divMsg" runat="server" style="color:Red;"></div>                     </td>                   </tr>                   <tr>                     <td colspan="3" class="tdControl">                         还原数据库时请关闭所有和该数据库连接的程序！                     </td>                   </tr>                   <tr>                     <td colspan="3" class="tdControl">                         <asp:RadioButton ID="sysDirectory" runat="server" Text="系统目录" Checked="True"                             GroupName="aa" οnclick="selectRadioButton()" />                         &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                         <asp:RadioButton ID="otherDirectory" runat="server" Text="其他目录"                             GroupName="aa" οnclick="selectRadioButton()" />                     </td>                   </tr>                   <tr id="trSys" runat="server">                     <td class="tdControl" style="text-align:right; width:200px;">系统目录文件：</td>                     <td colspan="2" class="tdControl">                         <asp:Panel ID="Panel1" runat="server">                             <asp:ListBox ID="DBList" runat="server" Width="285px" Height="108px"></asp:ListBox>                         </asp:Panel>                     </td>                   </tr>                   <tr id="trOther" runat="server" style="display:none;">                     <td class="tdControl" style="text-align:right; width:200px;">其他目录文件：</td>                     <td colspan="2" class="tdControl">                         <asp:FileUpload ID="FileUpload1" runat="server" Width="368px" />                     </td>                   </tr>                   <tr>                     <td colspan="3" style="padding-left:400px;" class="tdControl">                         <asp:Button ID="btnRestore" runat="server" Text="还  原"  CssClass="btn"                             οnclick="btnRestore_Click"  />                      </td>                   </tr>                 </tbody>               </table>              </td>             <td ></td>           </tr>           <tr>             <td ><IMG alt="" src="../images/r_4.gif"></td>             <td></td>             <td><IMG alt="" src="../images/r_3.gif"></td>           </tr>            </tbody>         </table>     </div>     </form> </body> </html>

 

public partial class DBRestore : System.Web.UI.Page     {         protected void Page_Load(object sender, EventArgs e)         {             if (!IsPostBack)             {                 GetBackUpFile();             }         }

        protected void btnRestore_Click(object sender, EventArgs e)         {             bakServer bak;             string strPath = "";             try             {                 bak = new bakServer();                 if (this.sysDirectory.Checked)                 {                     if (this.DBList.Items.Count > 0)                     {                         if (this.DBList.SelectedIndex != -1)                         {                             this.divMsg.InnerHtml = "数据正在还原中，请稍后……";                             this.btnRestore.Enabled = false;                             strPath = Server.MapPath("/DBBackUp/") + this.DBList.SelectedItem.Text;                             if (bak.RestoreDB(strPath))                             {                                 this.divMsg.InnerHtml = "数据库还原成功。";                                 this.btnRestore.Enabled = true;                             }                             else                             {                                 this.divMsg.InnerHtml = "数据库还原失败。";                                 this.btnRestore.Enabled = true;                             }                         }                         else                         {                             this.divMsg.InnerHtml = "请选择要还原的数据库文件。";                         }                     }                     else                     {                         this.divMsg.InnerHtml = "没有可还原的数据库文件。";                         this.btnRestore.Enabled = true;                     }                 }                 if (this.otherDirectory.Checked)                 {

                    if (FileUpload1.HasFile)                     {                         this.divMsg.InnerHtml = "数据正在还原中，请稍后……";                         this.btnRestore.Enabled = false;                         // 获取要上传文件的名称                         string FileName = this.FileUpload1.FileName;                         // 获取上传文件的扩展名                         string FileExtension = FileName.Substring(FileName.LastIndexOf(".") + 1);                                                 if (FileExtension == "bak")                         {                             strPath = this.FileUpload1.PostedFile.FileName;                             if (bak.RestoreDB(strPath))                             {                                 this.divMsg.InnerHtml = "数据库还原成功。";                                 this.btnRestore.Enabled = true;                             }                             else                             {                                 this.divMsg.InnerHtml = "数据库还原失败。";                                 this.btnRestore.Enabled = true;                             }                         }                         else                         {                             this.divMsg.InnerHtml = "不是正确的数据库还原文件。";                             this.btnRestore.Enabled = true;                         }                     }                     else                     {                         this.divMsg.InnerHtml = "请选择要还原的数据库文件。";                         this.btnRestore.Enabled = true;                     }                     this.ClientScript.RegisterStartupScript(GetType(), "display", "<script>document.getElementById('trSys').style.display='none';document.getElementById('trOther').style.display='block';</script>");                 }             }             catch (Exception ex)             {                 Session["errorMsg"] = ex.Message;                 Response.Redirect("Error.aspx", false);             }         }         /// <summary>         /// 加载数据备份文件         /// </summary>         private void GetBackUpFile()         {             try             {                 DirectoryInfo dir = new DirectoryInfo(Server.MapPath("/DBBackUp/"));                 foreach (FileInfo dChild in dir.GetFiles("*.bak"))                 {                     this.DBList.Items.Add(dChild.Name);                 }             }             catch (Exception ex)             {                 Session["errorMsg"] = ex.Message;                 Response.Redirect("Error.aspx", false);             }         }     }