asp.net 数据库备份与还原

最近在替学校做网站,今天刚大致做好了数据库备份与还原功能,因为网站图片是存放在文件下,所以备份数据库时,图片文件夹要一同考拷贝.具体如下:

DbOper类:

using System.IO;
///
///
public sealed class DbOper
{
///
/// DbOper类的构造函数
///
private DbOper()
{
     
}
     /// <summary>
     /// 复制文件
     /// </summary>
     /// <param name="from">要复制文件的源地址</param>
     /// <param name="to">要复制到的路径</param>
public static void copyfolder(string from, string to)
{
      string fname = from.Substring(from.LastIndexOf("\\") + 1);
      if (Directory.Exists(from))
      {
          Directory.CreateDirectory(from);
      }
      DirectoryInfo di = new DirectoryInfo(from);
      if (!Directory.Exists(to + "\\" + di.Name))
      {
          Directory.CreateDirectory(to + "\\" + di.Name);
      }
      string[] fi = Directory.GetFiles(di.FullName);
      for (int i = 0; i < fi.Length; i++)
      {
          string f_name = fi[i].Substring(fi[i].LastIndexOf("\\") + 1);
          File.Copy(from + "\\" + f_name, to + "\\" + fname + "\\" + f_name, true);
      }
      DirectoryInfo[] dis = di.GetDirectories();
      for (int j = 0; j < dis.Length; j++)
      {
          copyfolder(dis[j].FullName.ToString(), to + "\\" + fname);
      }
}
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="pathfilename">备份的路径</param>
/// <returns>返回结果信息</returns>
public static string DbBackup(string pathfilename)
{
      string ret;
      SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
      SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
      try
      {
          oSQLServer.LoginSecure = true;//验证模式，FALSE 是sql server验证模式 True 是混合验证模式
          oSQLServer.Connect(".", "", "");// 联接数据服务
          oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;// 恢复类型数据库
          oBackup.Database = "TzcAlumni";//数据库名称
          oBackup.Files = @pathfilename;//备份文件名,如果是多个设备上的需使用.devices属性
          oBackup.BackupSetName = "TzcAlumni";//要备份的数据库
          oBackup.BackupSetDescription = "数据库备份";//描述说明
          oBackup.Initialize = true;//表示是追加备份还是重新备份，原来如果有同名的备份是否覆盖它
          oBackup.SQLBackup(oSQLServer);
          ret = "备份成功";
      }
      catch (Exception x)
      {
          ret = "备份失败-" + x.ToString();
      }
      finally
      {
          oSQLServer.DisConnect();
      }
      return ret;
}

/// <summary>
/// 数据库恢复
/// </summary>
/// <param name="pathfilename">备份数据所在的路径</param>
/// <returns>返回结果信息</returns>
public static string DbRestore(string pathfilename)
{
      string ret;
      SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
      SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
      try
      {
          oSQLServer.LoginSecure = true;
          oSQLServer.Connect(".", "", "");
          oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
          oRestore.Database = "TzcAlumni";
          oRestore.Files = @pathfilename;
          oRestore.FileNumber = 1;//文件在设备上的ID号如果你只有一个文件就是1
          oRestore.ReplaceDatabase = true;//替代现有数据库如不存则创建他
          oRestore.SQLRestore(oSQLServer);//调用恢复方法
          ret = "成功恢复";
      }
      catch(Exception x)
      {
          ret = "恢复失败-" + x.ToString();
      }
      finally
      {
          oSQLServer.DisConnect();
      }
      return ret;
}
}

前台:

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <div style="margin: 20px 20px 20px 20px;">
                <asp:Button ID="Button1" runat="server" Text="备份数据库" OnClick="Button1_Click" />
                <asp:GridView ID="GridView1" runat="server" OnRowCommand="GridView1_RowCommand">
                    <Columns>
                        <asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="false" CommandArgument="<%#Container.DataItem%>"
                                    CommandName="recover" Text="&lt;div onclick=&quot;JavaScript:return confirm('确定恢复该数据库备份吗？')&quot;&gt;还还数据库&lt;/div&gt;"></asp:LinkButton>
                            </ItemTemplate>
                            <ItemStyle CssClass="trow" Width="60px" />
                        </asp:TemplateField>
                        <asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="false" CommandArgument="<%#Container.DataItem%>"
                                    CommandName="deleted" Text="&lt;div onclick=&quot;JavaScript:return confirm('确定删除该数据库备份吗？')&quot;&gt;删除&lt;/div&gt;">< /asp:LinkButton>
                            </ItemTemplate>
                            <ItemStyle CssClass="trow" Width="40px" />
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
                <asp:Label ID="Label2" runat="server"></asp:Label>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
    <asp:UpdateProgress ID="UpdateProgress1" runat="server">
        <ProgressTemplate>
            <img src="Images/等待.gif" />
            <br />
            <asp:Label ID="Label1" runat="server" Text="正在执行相关操作，请稍后..."></asp:Label>
        </ProgressTemplate>
    </asp:UpdateProgress>

后台:

protected void Page_Load(object sender, EventArgs e)
    {
        BindData();
    }
    private void BindData()
    {
        string[] path = Directory.GetDirectories(@Server.MapPath("Date/DbBackup/"));//路径
        ArrayList str = new ArrayList();
        foreach (string fname in path)
        {
            str.Add(fname.Substring(Server.MapPath("Date/DbBackup/").Length));
        }
        GridView1.DataSource = str;
        GridView1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string strpath = Server.MapPath("Date/DbBackup/") + System.DateTime.Now.ToString().Replace(":", "").Replace(" ", "");
            Directory.CreateDirectory(strpath);//创建一个新的文件夹
            string path = "[" + strpath + "/TzcAlumni" + ".bak]";//备份路径及文件名
             DbOper.DbBackup(path);//备份数库库
             DbOper.copyfolder(Server.MapPath("../Class/ClassPhotos"), strpath);//复制照片文件夹
             ((Label)UpdateProgress1.FindControl("Label1")).Text = "备份成功";
             BindData();
             CMessageBox.ShowAjaxDialog(UpdatePanel1,"备份成功");
        }
        catch (Exception x)
        {
            ((Label)UpdateProgress1.FindControl("Label1")).Text = "备份失败";
            CMessageBox.ShowAjaxDialog(UpdatePanel1, "备份失败-" + x.ToString());
        }
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.ToString() == "deleted")
        {
            string AdminID = Session["AdminID"].ToString();
            Directory.Delete(Server.MapPath("Date/DbBackup/") + e.CommandArgument.ToString(), true);//删除所选的备份文件夹
            BindData();
            CMessageBox.ShowAjaxDialog(UpdatePanel1, "删除成功");
            Session["AdminID"] = AdminID;
            return;
        }
        if (e.CommandName.ToString() == "recover")
        {

            try
            {
                KillSpidDataClassesDataContext kdb = new KillSpidDataClassesDataContext();
                kdb.killspid("TzcAlumni");//关闭用户与数据库的连接
                string strpath = "[" + Server.MapPath("Date/DbBackup/") + e.CommandArgument.ToString() +"/TzcAlumni" + ".bak]";
                Directory.Delete(Server.MapPath("../Class/ClassPhotos"), true);//删除原来的照片文件夹
                DbOper.copyfolder(Server.MapPath("../Admin/Date/DbBackup/" + e.CommandArgument.ToString() + "/") + "ClassPhotos", Server.MapPath("../Class"));//复制照片文件夹
                string ret = DbOper.DbRestore(strpath);//还原数据库
                Label2.Text = ret;
                CMessageBox.ShowAjaxDialog(UpdatePanel1, "1");
                return;
            }
            catch (Exception x)
            {
                CMessageBox.ShowAjaxDialog(UpdatePanel1,"数据恢复失败-"+x.ToString());
            }
        }
    }

killspid存储过程:

----------------断开所有用户打开的连接,关闭数据库
use master
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[killspid]
GO

create proc killspid
@dbname sysname --要关闭进程的数据库名
as
declare @s nvarchar(1000)
declare tb cursor local for
select s='kill '+cast(spid as varchar)
from master..sysprocesses
where dbid=db_id(@dbname)

open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go

大部分代码是从网上搜集而来,发上来以便自己以后查阅,希望能给有同样需求的朋友一个参考.