时间：2021-07-01 10:21:17 帮助过：10人阅读
- <br><%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlDbMgmt.aspx.cs" Inherits="SysSourceMgmt.SqlDbMgmt" %> <br><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <br><html xmlns="http://www.w3.org/1999/xhtml"> <br><head runat="server"> <br><title></title> <br></head> <br><body> <br><form id="form1" runat="server"> <br><div> <br><table> <br><tr> <br><td style="width: 100px"> <br><span style="font-size: 9pt">操 作 数 据 库</span> <br></td> <br><td> <br><asp:DropDownList ID="DropDownList1" runat="server" Font-Size="9pt" Width="124px"> <br></asp:DropDownList> <br><asp:TextBox ID="txtDbName" runat="server"></asp:TextBox> <br></td> <br><td style="width: 100px"> <br></td> <br></tr> <br><tr> <br><td style="width: 100px"> <br><span style="font-size: 9pt">备份名称和位置</span> <br></td> <br><td style="width: 100px"> <br><asp:TextBox ID="TextBox1" runat="server" Font-Size="9pt" Width="117px"></asp:TextBox> <br></td> <br><td style="width: 100px"> <br><span style="font-size: 9pt; color: #ff3300">（如D:\beifen）</span> <br></td> <br></tr> <br><tr> <br><td colspan="3"> <br><asp:Button ID="Button1" runat="server" Font-Size="9pt" OnClick="Button1_Click" Text="备份数据库" /> <br></td> <br></tr> <br></table> <br></div> <br><div style="width: 100%; height: 100px"> <br><table> <br><tr> <br><td style="width: 100px; height: 21px"> <br><span style="font-size: 9pt">操 作 数 据 库</span> <br></td> <br><td> <br><asp:DropDownList ID="DropDownList2" runat="server" Font-Size="9pt" Width="124px"> <br></asp:DropDownList> <br></td> <br><td style="width: 100px; height: 21px"> <br></td> <br></tr> <br><tr> <br><td style="width: 100px"> <br><span style="font-size: 9pt">操 作 数 据 库</span> <br></td> <br><td style="width: 100px"> <br><asp:FileUpload ID="FileUpload1" runat="server" Font-Size="9pt" Width="190px" /> <br></td> <br><td style="width: 100px"> <br></td> <br></tr> <br><tr> <br><td colspan="3"> <br><asp:Button ID="Button2" runat="server" Font-Size="9pt" OnClick="Button2_Click" Text="还原数据库" /> <br><asp:Button ID="Button3" runat="server" Font-Size="9pt" OnClick="Button3_Click" Text="强制还原数据库" /> <br></td> <br></tr> <br></table> <br></div> <br></form> <br></body> <br></html> <br> <br><strong>后台：</strong> <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>using System; <br>using System.Collections.Generic; <br>using System.Linq; <br>using System.Web; <br>using System.Web.UI; <br>using System.Web.UI.WebControls; <br>using System.Data.SqlClient; <br>using System.IO; <br>using System.Data; <br>using System.Diagnostics; <br>namespace SysSourceMgmt <br>{ <br>public partial class SqlDbMgmt : System.Web.UI.Page <br>{ <br>protected void Page_Load(object sender, EventArgs e) <br>{ <br>if (!IsPostBack) <br>{ <br>try <br>{ <br>string SqlStr1 = "Server=(local);DataBase=master;Uid=sa;Pwd="; <br>string SqlStr2 = "Exec sp_helpdb"; <br>SqlConnection con = new SqlConnection(SqlStr1); <br>con.Open(); <br>SqlCommand com = new SqlCommand(SqlStr2, con); <br>SqlDataReader dr = com.ExecuteReader(); <br>this.DropDownList1.DataSource = dr; <br>this.DropDownList1.DataTextField = "name"; <br>this.DropDownList1.DataBind(); <br>dr.Close(); <br>con.Close(); <br>SqlStr1 = "Server=(local);DataBase=master;Uid=sa;Pwd="; <br>SqlStr2 = "Exec sp_helpdb"; <br>con = new SqlConnection(SqlStr1); <br>con.Open(); <br>com = new SqlCommand(SqlStr2, con); <br>dr = com.ExecuteReader(); <br>this.DropDownList1.DataSource = dr; <br>this.DropDownList1.DataTextField = "name"; <br>this.DropDownList1.DataBind(); <br>dr.Close(); <br>con.Close(); <br>} <br>catch (Exception) <br>{ <br>} <br>} <br>} <br>protected void Button1_Click(object sender, EventArgs e) <br>{ <br>string dbName = string.Empty; <br>if (DropDownList1.Items.Count != 0) <br>{ <br>dbName = DropDownList1.SelectedValue.Trim(); <br>} <br>else <br>{ <br>dbName = txtDbName.Text.Trim(); <br>} <br>string SqlStr1 = "Data Source=.\\sqlexpress;Initial Catalog='" + dbName + "';Integrated Security=True"; <br>string SqlStr2 = "backup database " + dbName + " to disk='" + this.TextBox1.Text.Trim() + ".bak'"; <br>SqlConnection con = new SqlConnection(SqlStr1); <br>con.Open(); <br>try <br>{ <br>if (File.Exists(this.TextBox1.Text.Trim())) <br>{ <br>Response.Write("<script language=javascript>alert('此文件已存在，请从新输入！');location='Default.aspx'</script>"); <br>return; <br>} <br>SqlCommand com = new SqlCommand(SqlStr2, con); <br>com.ExecuteNonQuery(); <br>Response.Write("<script language=javascript>alert('备份数据成功！');'</script>"); <br>} <br>catch (Exception error) <br>{ <br>Response.Write(error.Message); <br>Response.Write("<script language=javascript>alert('备份数据失败！')</script>"); <br>} <br>finally <br>{ <br>con.Close(); <br>} <br>} <br>protected void Button2_Click(object sender, EventArgs e) <br>{ <br>string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 <br>string dbName = string.Empty; <br>if (DropDownList1.Items.Count != 0) <br>{ <br>dbName = DropDownList1.SelectedValue.Trim(); <br>} <br>else <br>{ <br>dbName = txtDbName.Text.Trim(); <br>} <br>string SqlStr1 = "Data Source=.\\sqlexpress;Initial Catalog='" + dbName + "';Integrated Security=True"; <br>string SqlStr2 = @"use master restore database " + dbName + " from disk='" + path + "'"; <br>SqlConnection con = new SqlConnection(SqlStr1); <br>con.Open(); <br>try <br>{ <br>SqlCommand com = new SqlCommand(SqlStr2, con); <br>com.ExecuteNonQuery(); <br>Response.Write("<script language=javascript>alert('还原数据成功！');'</script>"); <br>} <br>catch (Exception error) <br>{ <br>Response.Write(error.Message); <br>Response.Write("<script language=javascript>alert('还原数据失败！')</script>"); <br>txtDbName.Text = SqlStr2; <br>} <br>finally <br>{ <br>con.Close(); <br>} <br>} <br>/// <summary> <br>/// 恢复数据库，可选择是否可以强制还原（即在其他人在用的时候，依然可以还原） <br>/// </summary> <br>/// <param name="databasename">待还原的数据库名称</param> <br>/// <param name="databasefile">带还原的备份文件的完全路径</param> <br>/// <param name="errormessage">恢复数据库失败的信息</param> <br>/// <param name="forceRestore">是否强制还原（恢复），如果为TRUE，则exec killspid '数据库名' 结束此数据库的进程，这样才能还原数据库</param> <br>/// <returns></returns> <br>public bool RestoreDataBase(string databasename, string databasefile, ref string returnMessage, bool forceRestore, SqlConnection conn) <br>{ <br>bool success = true; <br>string path = databasefile; <br>string dbname = databasename; <br>string restoreSql = "use master;"; <br>if (forceRestore)//如果强制回复 <br>restoreSql += string.Format("use master exec killspid '{0}';", databasename); <br>restoreSql += "restore database @dbname from disk = @path;"; <br>SqlCommand myCommand = new SqlCommand(restoreSql, conn); <br>myCommand.Parameters.Add("@dbname", SqlDbType.Char); <br>myCommand.Parameters["@dbname"].Value = dbname; <br>myCommand.Parameters.Add("@path", SqlDbType.Char); <br>myCommand.Parameters["@path"].Value = path; <br>Response.Write(restoreSql); <br>try <br>{ <br>myCommand.Connection.Open(); <br>myCommand.ExecuteNonQuery(); <br>returnMessage = "还原成功"; <br>} <br>catch (Exception ex) <br>{ <br>returnMessage = ex.Message; <br>success = false; <br>} <br>finally <br>{ <br>myCommand.Connection.Close(); <br>} <br>return success; <br>} <br>protected void Button3_Click(object sender, EventArgs e) <br>{ <br>string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称 <br>string dbName = string.Empty; <br>if (DropDownList1.Items.Count != 0) <br>{ <br>dbName = DropDownList1.SelectedValue.Trim(); <br>} <br>else <br>{ <br>dbName = txtDbName.Text.Trim(); <br>} <br>string returnMessage = string.Empty; <br>string SqlStr1 = "Data Source=.\\sqlexpress;Initial Catalog='" + dbName + "';Integrated Security=True"; <br>SqlConnection con = new SqlConnection(SqlStr1); <br>RestoreDataBase(txtDbName.Text, path, ref returnMessage, true,con); <br>Response.Write(returnMessage); <br>} <br>} <br>} <br> <br></li><li><p>效果图：</p></li><li><p align="center"><img height="202" alt="" src="https://img.gxlcms.com//Uploads-s/new/2019-09-19-201919/20100425214721118.jpg" width="508" border="0"></p></li><li><p align="center"> </p></li><li><p align="left">经过试验，大体完成了我需要的功能，具体优化后期进行中。</p></li><li> </li><li> </li></ol></pre>