.net数据库备份还原方法

web网站数据定时备份是不可少的，我们开的网也会经常碰到网站备份这个功能，下面我们来介绍一下关于asp教程.net对mssqlserver数据库教程备份还原的方法吧。

using System.Data.SqlClient;

using System.IO;
string SqlStr1 = "Server=(local);DataBase=master;Uid=sa;Pwd=";
string SqlStr2 = "Exec sp_helpdb";
string SqlStr1 = "Server=(local);database='" + this.DropDownList1.SelectedValue + "';Uid=sa;Pwd=";
string SqlStr2 = "backup database " + this.DropDownList1.SelectedValue + " to disk='" + this.TextBox1.Text.Trim() + ".bak'";

2.后台

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 SqlStr1 = "Server=(local);DataBase=master;Uid=sa;Pwd=";
string SqlStr2 = "Exec sp_helpdb";
SqlConnection con = new SqlConnection(SqlStr1);
con.Open();
SqlCommand com = new SqlCommand(SqlStr2, con);
SqlDataReader dr = com.ExecuteReader();
this.DropDownList1.DataSource = dr;
this.DropDownList1.DataTextField = "name";
this.DropDownList1.DataBind();
dr.Close();
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string SqlStr1 = "Server=(local);database='" + this.DropDownList1.SelectedValue + "';Uid=sa;Pwd=";
string SqlStr2 = "backup database " + this.DropDownList1.SelectedValue + " to disk='" + this.TextBox1.Text.Trim() + ".bak'";
SqlConnection con = new SqlConnection(SqlStr1);
con.Open();
try
{
if (File.Exists(this.TextBox1.Text.Trim()))
{
Response.Write(" ");
return;
}
SqlCommand com = new SqlCommand(SqlStr2, con);
com.ExecuteNonQuery();
Response.Write(" ");
}
catch (Exception error)
{
Response.Write(error.Message);
Response.Write(" ");
}
finally
{
con.Close();
}
}
}
还原SqlServer
核心技术:
string SqlStr1 = "Server=(local);database='" + this.DropDownList1.SelectedValue + "';Uid=sa;Pwd=";
string SqlStr2 = "use master restore database " + dbname + " from disk='" + path + "'";
1.前台
操 作 数 据 库

操 作 数 据 库

 

 
 

2.后台
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 SqlStr1 = "Server=(local);DataBase=master;Uid=sa;Pwd=";
string SqlStr2 = "Exec sp_helpdb";
SqlConnection con = new SqlConnection(SqlStr1);
con.Open();
SqlCommand com = new SqlCommand(SqlStr2, con);
SqlDataReader dr = com.ExecuteReader();
this.DropDownList1.DataSource = dr;
this.DropDownList1.DataTextField = "name";
this.DropDownList1.DataBind();
dr.Close();
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string path = this.FileUpload1.PostedFile.FileName; //获得备份路径及数据库名称
string dbname = this.DropDownList1.SelectedValue;
string SqlStr1 = "Server=(local);database='" + this.DropDownList1.SelectedValue + "';Uid=sa;Pwd=";
string SqlStr2 = "use master restore database " + dbname + " from disk='" + path + "'";
SqlConnection con = new SqlConnection(SqlStr1);
con.Open();
try
{
SqlCommand com = new SqlCommand(SqlStr2, con);
com.ExecuteNonQuery();
Response.Write(" ");
}
catch (Exception error)
{
Response.Write(error.Message);
Response.Write(" ");
}
finally
{
con.Close();
}
}
}

网上找的另一种备份还原数据库的方法

.net实现数据库备份及还原 收藏

/// <summary>
  /// 备份数据库
  /// </summary>
  private void btnBackUp_Click(object sender, System.EventArgs e)
  {
   this.Cursor = Cursors.WaitCursor;
   this.label1.Text = "  正在进行档案库的数据备份，这可能需要几秒到几十的时间，请稍候...";
   this.label1.Visible = true;
   this.label1.Refresh();
   this.pBar1.Visible = true;
   //------------------------------------------------------------------------------------
  
   string selfName = “D:NorthwindBak“;
   string deviceName = “NorthwindBak“;
   string remark = "备份测试";
  
   //◆数据备份：
   SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
   SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
   oBackup.Action = 0 ;
   oBackup.Initialize = true ;
   SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
   oBackup.PercentComplete += pceh;

   try
   {
    oSQLServer.LoginSecure = false;
    oSQLServer.Connect(Common.MySettings.SqlServerName, "sa", "");
    oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
    oBackup.Database = "Northwind";//数据库名
    oBackup.Files = selfName;//文件路径
    oBackup.BackupSetName = deviceName;//备份名称
    oBackup.BackupSetDescription = remark;//备份描述
    oBackup.Initialize = true;
    oBackup.SQLBackup(oSQLServer);
   
   }
   catch(System.Exception ex)
   {
    Common.ShowMsg("数据备份失败：n" + ex.ToString());
   }
   finally
   {
    oSQLServer.DisConnect();
   }

   //------------------------------------------------------------------------------------
   this.label1.Visible = false;
   this.pBar1.Visible = false;
   this.Cursor = Cursors.Default;
  }

  /// <summary>
  /// 显示备份进度条
  /// </summary>
  private void Step(string message,int percent)
  {
   this.pBar1.Value = percent ;
  }

还原方法

数据还原 ：
  /// <summary>
  /// 还原数据库
  /// </summary>
  private void btnRestore_Click(object sender, System.EventArgs e)
  {
  
   this.Cursor = Cursors.WaitCursor;
   this.label1.Text = "  正在进行档案库的数据还原，这可能需要几秒到几十的时间，请稍候...";
   this.label1.Visible = true;
   this.label1.Refresh();
   this.pBar1.Visible = true;
   //------------------------------------------------------------------------------------

   string fileName = "NorthwindBak";
   string filePath = "D:NorthwindBak";
   string remark = "备份测试";

   SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
   SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
   oRestore.Action = 0 ;
   SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
   oRestore.PercentComplete += pceh;
   try
   {
                oSQLServer.Connect(Common.MySettings.SqlServerName, "sa", "");
                SQLDMO.QueryResults qr = oSQLServer.EnumProcesses(-1) ;
                int iColPIDNum = -1 ;
                int iColDbName = -1 ;

  //杀死其它的连接进程
                for(int i=1;i<=qr.Columns;i++)
                {
                    string strName = qr.get_ColumnName(i) ;
                    if (strName.ToUpper().Trim() == "SPID")
                    {
                        iColPIDNum = i ;
                    }
                    else if (strName.ToUpper().Trim() == "DBNAME")
                    {
                        iColDbName = i ;
                    }
                    if (iColPIDNum != -1 && iColDbName != -1)
                        break ;
                }

                for(int i=1;i<=qr.Rows;i++)
                {
                    int lPID = qr.GetColumnLong(i,iColPIDNum) ;
                    string strDBName = qr.GetColumnString(i,iColDbName) ;
                    if (strDBName.ToUpper() == "CgRecord".ToUpper())
                        oSQLServer.KillProcess(lPID) ;
                }

    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
    oRestore.Database = "Northwind";
    oRestore.Files = filePath;
    oRestore.FileNumber = 1;
    oRestore.ReplaceDatabase = true;
    oRestore.SQLRestore(oSQLServer);

               
  }
            catch(System.Exception ex)
            {
                Common.ShowMsg("数据还原失败：n" + ex.ToString());
   }
   finally
   {
    oSQLServer.DisConnect();
   }
 
   //------------------------------------------------------------------------------------
   this.label1.Visible = false;
   this.pBar1.Visible = false;
   this.Cursor = Cursors.Default;
  }

  /// <summary>
  /// 显示还原进度条
  /// </summary>
  private void Step(string message,int percent)
  {
   this.pBar1.Value = percent ;
  }