首页 > 程序开发 > .NET开发 > asp.net备份恢复sql server数据库
网站建设、切图、企业建站、微信开发 联系QQ:404658186 tel:18716186715
2015
06-30

asp.net备份恢复sql server数据库

文章目录 [收起]

在做asp.net系统开发时,备份数据库恢复数据库是必要的部份,这里就来了解一下如何对sql server备份和恢复数据库。

首先看前台代码,里面有对数据库的备份,删除,恢复等操作按钮,然后在后台写出对应的代码。

<asp:Content ID="Content3" ContentPlaceHolderID="c" runat="server">
<div id="main"> 
<div class="pos">
<table>
<tr>
<td>数据库管理:</td>
<td><div class="submit">
 <asp:Button ID="backdb" runat="server" Text="备份数据库" style="text-align:center;" 
 onclick="backdb_Click" /></div></td>
</tr>
</table></div>
<div style="clear:both;margin:10px 0px;"></div>
<table class="table_list" cellspacing="0" cellpadding="0" border="0">
<thead>
 
<tr>
<th>备份名称</th>
<th width="100">操作</th>
</tr>
</thead>
<tbody> 
 <asp:Repeater ID="ReptBackDBList" runat="server" OnItemCommand="ReptBackDBList_OnItemCommand">
 <ItemTemplate>
 <tr> 
 <td><%# Container.DataItem%> </td> 
 <td align="center"> 
 <asp:LinkButton ID="LinkBtnReStore" runat="server" CommandArgument='<%# Container.DataItem%> ' CommandName="ReStore" Text="恢复"></asp:LinkButton>
 |
 <asp:LinkButton ID="LinkBtnDel" runat="server" CommandArgument='<%# Container.DataItem%> ' OnClientClick="return confirm('确认要删除吗?')" CommandName="Del" Text="删除"></asp:LinkButton> 
 </td></tr>
 </ItemTemplate>
 </asp:Repeater>
</tbody>
</table> 
<div class="page">
 <asp:Literal ID="pages" runat="server"></asp:Literal> 
</div>
</div> 
</asp:Content>

列出已备份的数据库列表

读取数据库的备份目录,然后遍历文件,得到数据备份列表,并赋于Repeater.

private void getInfo()
{
      /**********dirPath 数据库备份目录 *****************/
      string dirPath = HttpContext.Current.Server.MapPath("~/data/");
      ArrayList arr = new ArrayList();
      if (Directory.Exists(dirPath))
      {
           //获得目录信息
           DirectoryInfo dir = new DirectoryInfo(dirPath);
           //获得目录文件列表
           FileInfo[] files = dir.GetFiles("*.*"); ;

           foreach (FileInfo fileInfo in files)
           {
                 string Text = fileInfo.Name;
                 arr.Add(Text);
           }
           arr.Sort();
           arr.Reverse();
       }
       ReptBackDBList.DataSource = arr; //数据源赋值
       ReptBackDBList.DataBind();       //绑定数据源
}

备份数据库

核心sql操作:backup DataBase    dbname   to disk= BackFilePath

protected void backdb_Click(object sender, EventArgs e)
        { 
            string BackFilePath = "";
            string BackName = "";
            DateTime date = DateTime.Now;
            string NowDate = date.ToString("yyyyMMddHHmmss");
            BackFilePath = Server.MapPath("/") + "data/" + NowDate + ".bak";
            BackName = NowDate + ".bak";
            if (System.IO.File.Exists(BackFilePath))
            {
                ClientScript.RegisterStartupScript(GetType(), "", "confirm('该文件已存在,是否要替换此文件?'", true);
            }
            using (SqlConnection connection = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    int Resoult = SqlHelper.ExecuteNonQuery(connection, CommandType.Text, "backup DataBase  " + dbname + "  to disk='" + BackFilePath + "'");//备份操作 
                    string pageUrl = HttpContext.Current.Request.Url.PathAndQuery;
                    logOper.addLog(Server.UrlDecode(Request.Cookies["uname"].Value )+ " 备份数据库 “" + BackName + "”"); //添加日志
                    ClientScript.RegisterStartupScript(GetType(), "", "alert('数据库备份成功!')", true);
                    getInfo(); //重新绑定数据源
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

恢复数据库和删除数据库备份

此操作需要在Repeater事件OnItemCommand里进行操作,删除备份就是删除对应目录的文件。

重点是恢复数据库,恢复步骤如下。

  1. 恢复数据库先要获得数据库的独占权。
    Alter Database  dbname  Set Offline with Rollback immediate ;
  2. 恢复sql操作
    RESTORE DATABASE   dbname FROM DISK = BackDBPath  WITH REPLACE;
  3. 解除独占权
    Alter Database   dbname  Set OnLine With rollback Immediate;
protected void ReptBackDBList_OnItemCommand(object source, RepeaterCommandEventArgs e)
        {

            if (e.CommandName == "Del")
            {  //删除备份
                string BackPath = Server.MapPath("/") + "data/" + e.CommandArgument.ToString();
                //ClientScript.RegisterStartupScript(GetType(), "", "alert('" + BackPath + "')", true);
                try
                {
                    if (common.FileDelete(BackPath))
                    {
                        logOper.addLog(Server.UrlDecode(Request.Cookies["uname"].Value) + " 删除数据库备份 “" + e.CommandArgument.ToString() + "”");
                        ClientScript.RegisterStartupScript(GetType(), "", "alert('备份删除成功!')", true);
                        getInfo();
                    }
                    else
                    {
                        ClientScript.RegisterStartupScript(GetType(), "", "alert('据库备份删除失败!')", true);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            if (e.CommandName == "ReStore")
            {
                //恢复备份数据库
                string BackDBPath = Server.MapPath("/") + "data/" + e.CommandArgument.ToString();
                StringBuilder StrSql = new StringBuilder();
                StrSql.Append("use master; ");
                StrSql.Append("Alter Database " + dbname + " Set Offline with Rollback immediate ;");
                StrSql.Append("RESTORE DATABASE  " + dbname + "  FROM DISK ='" + BackDBPath + "' WITH REPLACE;");
                StrSql.Append("Alter Database  " + dbname + "  Set OnLine With rollback Immediate; ");
                using (SqlConnection connection = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(connection, CommandType.Text, StrSql.ToString());
                        logOper.addLog(Server.UrlDecode(Request.Cookies["uname"].Value )+ " 还原数据库 “" + e.CommandArgument.ToString() + "”");
                        ClientScript.RegisterStartupScript(GetType(), "", "alert('数据库还原成功!')", true);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }


            }
        }

asp.net备份恢复sql server数据库就是这样了,虽说不太高明,但是基本能满足大部人的需求了,如有错误欢迎指正。

最后编辑:
作者:码农一号
这个作者貌似有点懒,什么都没有留下。
捐 赠如果您觉得这篇文章有用处,请支持作者!鼓励作者写出更好更多的文章!

留下一个回复

你的email不会被公开。

*