asp.net 备份和恢复数据库
观看了Insus的视频写下来的,代码可能有点冗长,如有好的想法的,可以多交流。
前台:
<form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="备份数据库" OnClick="Button1_Click" /> <asp:Label ID="Label1" runat="server" Text=""></asp:Label> <asp:Label ID="Label2" runat="server" Text=""></asp:Label> <asp:GridView ID="gv_DataBasefile" runat="server" AutoGenerateColumns="False" Width="100%" OnRowCommand="gv_DataBasefile_RowCommand"> <RowStyle HorizontalAlign="center" /> <Columns> <asp:TemplateField HeaderText="文件名"> <ItemTemplate> <%#Eval("Name") %> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="创建时间"> <ItemTemplate> <%# objIdtu.GetDateTime( Eval("CreationTime"),"yyyy-MM-dd HH:mm:ss")%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="修改时间"> <ItemTemplate> <%# objIdtu.GetDateTime(Eval("LastWriteTime"), "yyyy-MM-dd HH:mm:ss")%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="路径"> <ItemTemplate> <%# Eval("DirectoryName")%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="还原"> <ItemTemplate> <%--CommandArgument 传递两个数据 Name,DirectoryName--%> <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="../images/x1root.gif" CommandArgument='<%# Eval("Name")+","+Eval("DirectoryName")%>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form>
后台:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using HanSoft.BusinessLogic; using Insus.NET; using CAF.DBUtility; using System.IO; using System.Data; using System.Data.SqlClient; using System.Collections; using HanSoft.SQLServerDAL; public partial class information_backDB : System.Web.UI.Page { protected InsusDateTimeUtility objIdtu = new InsusDateTimeUtility(); InsusIOUtility objIotu = new InsusIOUtility(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Data_Binding(); } } private void Data_Binding() { DirectoryInfo dInfo = new DirectoryInfo(Server.MapPath("~/backSql")); this.gv_DataBasefile.DataSource = dInfo.GetFiles(); this.gv_DataBasefile.DataBind(); } /// <summary> /// 备份数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button1_Click(object sender, EventArgs e) { try { BACKUP(); Label1.Text = "数据库备份成功!"; Data_Binding(); } catch (Exception ex) { Response.Write(ex.Message); } } /// <summary> /// 备份数据库方法 /// </summary> private void BACKUP() { string DBname = "1220"; string backUpDBname = DBname + "_" + objIdtu.GetDateTimeMillisecond();//这一步是数据库名+年月日+随机数 objIotu.MakeDirectory(Server.MapPath("~/backSql")); //这一步是在根目录下面生成一个文件夹,名叫 backsql string str_sql = " BACKUP DATABASE [" + DBname + "] TO DISK = '" + Server.MapPath("~/").ToString() + "backSql\\" + backUpDBname + ".bak'"; int i = SqlHelper.ExecuteNonQuery(str_sql); } /// <summary> /// 还原数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void gv_DataBasefile_RowCommand(object sender, GridViewCommandEventArgs e) { SelectObjectIndexBLL SoIndexBLL = new SelectObjectIndexBLL(); SqlConnection connection = new SqlConnection("Data Source=192.168.1.98;User ID=sa;Password=123"); string path_name = e.CommandArgument.ToString(); string[] array = { }; string[] array1 = { }; array = path_name.Split(','); string dbFileName = array[1].ToString(); array1 = array[0].ToString().Split('_'); string dbName = array1[0].ToString(); SqlCommand command = new SqlCommand("use master;restore database @name from disk=@path WITH REPLACE;", connection); string path = dbFileName + "//" + array[0]; //全路径 command.Parameters.AddWithValue("@name", dbName); //dbName还原目标数据库名 command.Parameters.AddWithValue("@path", path); DataTable dt_spid = SoIndexBLL.GetDataList(" select spid from master..sysprocesses where dbid=db_id('1220') ").Tables[0]; if (dt_spid.Rows.Count > 0) { for (int i = 0; i < dt_spid.Rows.Count; i++) { string sql = " kill " + Convert.ToString(dt_spid.Rows[i]["spid"]) + " "; //SqlDataAdapter sqldap = new SqlDataAdapter(sql, connection);//--------------1 SqlCommand commd = new SqlCommand(sql, connection);//----------2 connection.Open(); commd.ExecuteReader();//-------------2 connection.Close(); } Label2.Text = " 数据库恢复成功! "; } connection.Open(); command.ExecuteNonQuery();//-----------------1 connection.Close(); } }
另外附上两个主键:
InsusDateTimeUtility.dll 和 InsusIOUtility.dll
http://115.com/lb/5lbcqlxnjq4b#InsusDateTimeUtility.dll 115网盘礼包码:5lbcqlxnjq4b
复制链接,接受礼包即可。
直如弦,死道边;曲如钩,反封侯