数据库的备份与还原操作
Default.aspx
View Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>备份数据库</title> </head> <body style="text-align: center"> <form id="form1" runat="server"> <div> <table cellpadding="0" cellspacing="0" style="width: 400px; height: 7px;"> <tr> <td style="height: 4px; text-align: left; width: 295px;"> </td> </tr> <tr> <td style="height: 18px; text-align: center; width: 295px;"> </td> </tr> <tr> <td style="text-align: center; width: 295px;"> <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True" Font-Size="9pt" RepeatDirection="Horizontal"> <asp:ListItem Selected="True">备份数据库</asp:ListItem> <asp:ListItem>还原数据库</asp:ListItem> </asp:RadioButtonList></td> </tr> <tr style="font-size: 12pt"> <td style="height: 6px; text-align: center; width: 295px;"> </td> </tr> <tr style="font-size: 12pt"> <td rowspan="1" style="text-align: center; width: 295px; height: 3px;"> <table cellpadding="0" cellspacing="0" width="100%" style="height: 11px"> <tr> <td style="width: 100px; text-align: center"> <span style="font-size: 9pt">操 作 数 据 库</span></td> <td style="text-align: left"> <asp:DropDownList ID="dropSqlName" runat="server" Font-Size="9pt" Width="190px"> </asp:DropDownList></td> </tr> </table> </td> </tr> <tr> <td style="text-align: center; width: 295px;"> <asp:Panel ID="Panel2" runat="server" Height="5px" Width="100%"> <table cellpadding="0" cellspacing="0" style="width: 106%; height: 112px"> <tr> <asp:Image ID="Image1" runat="server" ImageUrl="~/Images/beifentu.bmp" /> </tr> <tr> <td style="width: 100px; height: 25px; text-align: center"> <span style="font-size: 9pt">备份名称和位置</span></td> <td style="height: 25px; text-align: left;"> <asp:TextBox ID="TextBox1" runat="server" Font-Size="9pt" Width="183px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ErrorMessage="备份地址不能为空">*</asp:RequiredFieldValidator><span style="font-size: 9pt; color: #ff3300">（如D:\beifen）</span></td> </tr> </table> </asp:Panel> <asp:Panel ID="Panel1" runat="server" Visible="False" Width="100%" Height="5px"> <table cellpadding="0" cellspacing="0" width="100%"> <tr> <asp:Image ID="Image2" runat="server" ImageUrl="~/Images/huanyuantu.bmp" /> </tr> <tr> <td style="width: 100px; height: 21px; text-align: center;"> <span style="font-size: 9pt">还 原 数 据 库</span></td> <td colspan="2" style="height: 21px; text-align: left;"> <asp:FileUpload ID="fileShow" runat="server" Font-Size="9pt" Width="190px" /></td> </tr> </table> </asp:Panel> <table cellpadding="0" cellspacing="0" style="width: 99%; height: 167px;"> <tr> <td style="height: 1px; text-align: center;"> <asp:Panel ID="Panel3" Height="5px" runat="server"> <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/Images/btnbf.JPG" onclick="ImageButton1_Click" /></asp:Panel> <asp:Panel ID="Panel4" runat="server" Height="5px" Visible="False"> <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/Images/btnhyk.bmp" onclick="ImageButton2_Click" /></asp:Panel> <asp:ValidationSummary ID="ValidationSummary1" runat="server" Font-Size="9pt" ShowMessageBox="True" ShowSummary="False" /> </td> </tr> </table> </td> </tr> <tr> <td style="height: 19px; width: 295px;"> </td> </tr> </table> </div> </form> </body> </html>
Default.aspx.cs
View Code
using System; using System.Configuration; using System.Data; 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 System.Data.SqlClient; //引用命名空间 using System.IO; //引用命名空间 public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { string cmdtxt1 = ConfigurationSettings.AppSettings["strCon"]; //定义查询所有的数据库的SQL语句 string cmdtxt2 = "Exec sp_helpdb"; //创建数据库连接对象 SqlConnection Con = new SqlConnection(cmdtxt1); //打开数据库连接 Con.Open(); //创建命令对象 SqlCommand mycommand = new SqlCommand(cmdtxt2, Con); //创建一个数据阅读器 SqlDataReader dr = mycommand.ExecuteReader(); //将从数据库中读取到的数据作为dropSqlName数据源 this.dropSqlName.DataSource = dr; //指定文本内容 this.dropSqlName.DataTextField = "name"; //从数据库中绑定数据 this.dropSqlName.DataBind(); //关闭数据阅读器 dr.Close(); //关闭数据库连接 Con.Close(); } if (this.RadioButtonList1.SelectedIndex == 1) { this.Panel1.Visible = true; this.Panel2.Visible = false; this.Panel3.Visible = false; this.Panel4.Visible = true; } else { this.Panel2.Visible = true; this.Panel1.Visible = false; this.Panel3.Visible = true; this.Panel4.Visible = false; } } protected void ImageButton1_Click(object sender, ImageClickEventArgs e) { //定义数据库连接字串 string cmdtxt1 = "Server=localhost;database='" + this.dropSqlName.SelectedValue + "';Uid=sa;Pwd=123"; //定义备数据库的T-SQL命令的字符串 string cmdtxt2 = "backup database " + this.dropSqlName.SelectedValue + " to disk='" + this.TextBox1.Text.Trim() + ".bak'"; //创建数据库连接对象 SqlConnection Con = new SqlConnection(cmdtxt1); //打开数据库连接 Con.Open(); try { //判断上传文件是否空 if (File.Exists(this.TextBox1.Text.Trim())) { Response.Write("<script language=javascript>alert('此文件已存在，请从新输入！');location='Default.aspx'</script>"); return; } //创建命令对象 SqlCommand Com = new SqlCommand(cmdtxt2, Con); //执行数据库操作 Com.ExecuteNonQuery(); Response.Write("<script language=javascript>alert('备份数据成功！');location='Default.aspx'</script>"); } catch (Exception ms) { Response.Write(ms.Message); Response.Write("<script language=javascript>alert('备份数据失败！')</script>"); } finally { //关闭数据库连接 Con.Close(); } } protected void ImageButton2_Click(object sender, ImageClickEventArgs e) { //获得备份路径及数据库名称 string path = this.fileShow.PostedFile.FileName; //获取文件的后缀名 string last = path.Substring(path.LastIndexOf(".") + 1); string dbname = this.dropSqlName.SelectedValue; //判断是不是数据库备份文件 if (last == "bak") { //定义数据库连接字符串 string cmdtxt1 = "Server=localhost;database='" + this.dropSqlName.SelectedValue + "';Uid=sa;Pwd=123"; //定义实现还原数据库操作的字符串 string cmdtxt2 = "use master restore database " + dbname + " from disk='" + path + "'"; //定义数据库连接对象 SqlConnection Con = new SqlConnection(cmdtxt1); //打开数据库连接 Con.Open(); try { //创建命令对象 SqlCommand Com = new SqlCommand(cmdtxt2, Con); //执行数据库命令 Com.ExecuteNonQuery(); Response.Write("<script language=javascript>alert('还原数据成功！');location='Default.aspx'</script>"); } catch (Exception ms) { Response.Write(ms.Message); Response.Write("<script language=javascript>alert('还原数据失败！')</script>"); } finally { //关闭数据库连接 Con.Close(); } } else { Response.Write("<script language=javascript>alert('必须是数据库文件！');</script>"); } } }
