asp.net 备份和恢复数据库

作者:%e7%92%87%e7%8b%bc%e4%b9%8b%e9%a3%8e 和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

复制链接,接受礼包即可。

相关资料:

asp.net 备份和恢复数据库来源网络,如有侵权请告知,即处理!

编程Tags: