备份SQL数据库

从project菜单中选择add reference----COM--------- Microsoft DTSPackage Object Library与Microsoft SQLDMO Object Library

打开对象浏览器，展开Interop.SQLDMO，现实属性与方法 使用对象SQLServer,Backup,BackupDevices,Backupdevice,QueryResults 程序太大，留mail我发给你吧。 shaokui.wang@cn.abb.com

try    {     SQLDMO.Backup oBackup = new SQLDMO.BackupClass();     SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();     oSQLServer.LoginSecure = false;     oSQLServer.Connect("localhost", "sa", "1234");     oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;     oBackup.Database = "Northwind";     oBackup.Files = @"d:/Northwind.bak";     oBackup.BackupSetName = "Northwind";     oBackup.BackupSetDescription = "数据库备份";     oBackup.Initialize = true;     oBackup.SQLBackup(oSQLServer);    }    catch    {     throw;    }

 

   try    {     SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();     SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();     oSQLServer.LoginSecure = false;     oSQLServer.Connect("wellserver", "sa", "well");     oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;     oRestore.Database = "czwelldb";     oRestore.Files = @"d:/czwelldb.bak";     oRestore.FileNumber = 1;     oRestore.ReplaceDatabase = true;     oRestore.SQLRestore(oSQLServer);    }    catch    {     throw;    }

主　　题：  最近由于工作需要，写了个数据库的备份与恢复，贴出来与大家分享。   作　　者：  zhangqy (zhangqy)  信 誉 值：  90 所属论坛：  .NET技术 C# 问题点数：  0 回复次数：  3 发表时间：  2004-02-26 12:28:54           最近由于工作需要，写了个数据库的备份与恢复，贴出来与大家分享。 准备工作: 1.先在项目中添加backfile.xml文件，根结点为<root></root>. 2.打开库master,新建存储过程,旨在杀掉其它用户的访问。其中@dbname为备份数据库的名称(以上实例为hdnjw,根据你的数据库名改。)   CREATE  PROCEDURE killspid  @dbname  varchar(20) as begin declare  @sql  nvarchar(500),@temp varchar(1000) declare  @spid  int set  @sql='declare  getspid  cursor  for select  spid  from  master..sysprocesses  where  dbid=db_id('''+@dbname+''')'  exec  (@sql)   open  getspid   fetch  next  from  getspid  into  @spid while  @@fetch_status <> -1 begin   set @temp='kill  '+rtrim(@spid)   exec(@temp) fetch  next  from  getspid  into  @spid end close  getspid deallocate  getspid end GO 3.打开hdnjw,新建存储过程.   CREATE PROCEDURE  BackDataBase  @backPath varchar(100) AS          BACKUP DATABASE hdnjw TO DISK = @backPath  WITH NOINIT GO    4.根椐我的经验，最好添加一个能访问，操作数据表的用户。 以下为备份与恢复的源程序.

//备份数据库(hdnjw),备份文件存于../../databackup/下

  private void btnBack_Click(object sender, System.EventArgs e)   {    SqlDataBase.DbBase conn = new SqlDataBase.DbBase();    DateTime dt = DateTime.Now ;    string sPath = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() ;    System.IO.DirectoryInfo oD = new DirectoryInfo( Server.MapPath( "../../DataBackUp/" ) ) ;    string sLinkName =  "../../DataBackUp/" + sPath ;    //判断是否存在此目录,否则新建目录    if ( !oD.Exists )     oD.Create() ;    conn.BackDataBase( Server.MapPath( sLinkName ) ) ;    //XML格式为<root><backfile><id></id><title></title><createtime></createtime><linkname></linkname></backfile></root>             XmlDocument xmlDoc = new XmlDocument() ;    xmlDoc.Load( Server.MapPath( "backfile.xml" ) ) ;    //获取最后一个节点的递一个元素的值(ID) + 1 成为新的元素的ID值    XmlNode node = xmlDoc.LastChild.LastChild ;    int intID = 1 ;    if ( node != null )      intID += Convert.ToInt32( node.FirstChild.InnerText ) ;      //添加一个backfile节点    XmlElement elem = xmlDoc.CreateElement( "backfile" ) ;        XmlElement elem1 ;    XmlText xmlTxt ;

   //添加backfile节点的id元素    elem1 = xmlDoc.CreateElement( "id" ) ;    xmlTxt = xmlDoc.CreateTextNode( intID.ToString() ) ;         //文件名    elem1.AppendChild( xmlTxt ) ;    elem.AppendChild( elem1 ) ;

            //添加backfile节点的filename元素    elem1 = xmlDoc.CreateElement( "title" ) ;    xmlTxt = xmlDoc.CreateTextNode( txtTitle.Text.Trim() ) ;         //文件名    elem1.AppendChild( xmlTxt ) ;    elem.AppendChild( elem1 ) ;        //备份时间    elem1 = xmlDoc.CreateElement( "createtime" ) ;    xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( "yyyy-MM-dd" ) ) ;    elem1.AppendChild( xmlTxt ) ;    elem.AppendChild( elem1 ) ;        //文件路径    elem1 = xmlDoc.CreateElement( "linkname" ) ;    xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( sPath ) ) ;    elem1.AppendChild( xmlTxt ) ;    elem.AppendChild( elem1 ) ;

   //添加操作,方便DataGrid取数据    elem1 = xmlDoc.CreateElement( "dosomething" ) ;    xmlTxt = xmlDoc.CreateTextNode( "恢复" ) ;    elem1.AppendChild( xmlTxt ) ;    elem.AppendChild( elem1 ) ;        //添加backfile节点    xmlDoc.DocumentElement.AppendChild( elem ) ;    xmlDoc.Save( Server.MapPath( "backfile.xml" ) ) ;

   Response.Write( "<script language=javascript>alert('已成功备份!');var win =window.open('index.aspx','main');</script>"  ) ;     }

//恢复数据库

private void BackUpDataBase ( string strID ) {  string strPath = "" ;  XmlDataDocument dom=new XmlDataDocument();  dom.Load ( Server.MapPath(  "backfile.xml" ) ) ;  XmlElement root = dom.DocumentElement;  //查找id.value=Request["id"]的节点  XmlNode node = root.SelectSingleNode( "//backfile[id='"+strID +"']");  if( node != null )         //找到了  {   //文件路径节点   strPath = node.ChildNodes.Item(3).InnerText ;    }  if ( strPath != "" )  {   CommonOperation.Operation Common = new CommonOperation.Operation();   SqlDataBase.DbBase conn = new SqlDataBase.DbBase();   //以数据库管理员(sa)恢复数据,首先杀掉所有用户进程   conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConnAdmin"] ;   conn.DynamicStoredProcedure( "killspid" ) ;   SqlParameter sp_temp1;   sp_temp1 = conn.m_Command.Parameters.Add("@dbname",SqlDbType.VarChar,200);   sp_temp1.Direction = ParameterDirection.Input;   sp_temp1.Value = "hdnjw" ;   try   {    string FilePath = Server.MapPath( "../../DataBackUp/" + strPath ) ;    conn.ExecuteSqlForDynamic() ;    conn.ExecuteSql( "alter database hdnjw set RESTRICTED_USER  with rollback immediate" ) ;//恢复数据时禁止除SA外的任何人访问库    string sRestroe = "Restore DataBase hdnjw From disk='"+ FilePath +"' " ;    conn.ExecuteSql( sRestroe ) ;    conn.ExecuteSql( "alter database hdnjw set MULTI_USER  with rollback immediate" ) ;//充许访问库    conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConn"] ;    Response.Write( "<script language=javascript>alert('成功恢复数据!');window.close();</script>" ) ;   }   catch ( System.Exception ee )   {    Common.MsgBox( this,ee.ToString() ) ;   }   finally   {    conn.Close() ;   }  }   }           使用存储过程,根据需要传递相应参数即可

create proc BackupDB @dbname sysname='',  --要备份的数据库名称,不指定则备份当前数据库 @bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录 @bkfname nvarchar(260)='', --备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间 @bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份 @appendfile bit=1  --追加/覆盖备份文件 as  declare @sql varchar(8000)  if isnull(@dbname,'')='' set @dbname=db_name()  if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)  if isnull(@bkfname,'')='' set @bkfname='/DBNAME/_/DATE/_/TIME/.BAK'  set @bkfname=replace(replace(replace(@bkfname,'/DBNAME/',@dbname)   ,'/DATE/',convert(varchar,getdate(),112))   ,'/TIME/',replace(convert(varchar,getdate(),108),':',''))  set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname   +' to disk='''+@bkpath+@bkfname   +''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end   +case @appendfile when 1 then 'NOINIT' else 'INIT' end  print @sql  exec(@sql)

GO

 

SQL　SERVER备份： sql="backup database mydb to disk='"+nowpath+"//数据备份文件(不要删除).dll"+"' with format,name='Full Backup of TradeDb/SQL'";    try    {     SqlCommand  cmd = new SqlCommand(sql, conn);     cmd.ExecuteNonQuery();     MessageBox.Show("成功备份数据！备份记录为："+nowpath+"//数据备份文件(不要删除).dll"," 成功了！", MessageBoxButtons.OK, MessageBoxIcon.Information );    }    catch(Exception err)    {     MessageBox.Show("出现错误，请与作者联系！"+err.ToString() ,"出错了!",MessageBoxButtons.OK ,MessageBoxIcon.Question  );          }    finally    {     conn.Close();     this.Close();         }

 

 

还原： 　由于可能数据库被其它进程打开，所以写的一段SQL　SERVER的代码，在MASTER中存为一个存储过程： create  proc  killspid  (@dbname  varchar(20))  as  begin  declare  @sql  nvarchar(500)  declare  @spid  int  set  @sql='declare  getspid  cursor  for    select  spid  from  sysprocesses  where  dbid=db_id('''+@dbname+''')'  exec  (@sql)  open  getspid  fetch  next  from  getspid  into  @spid  while  @@fetch_status  <  >-1  begin  exec('kill  '+@spid)  fetch  next  from  getspid  into  @spid  end  close  getspid  deallocate  getspid  end  然后，在C＃代码里： try    {    string nowpath=Application.StartupPath;        //进入master      sql="use master";      SqlCommand cmd = new SqlCommand(sql, conn);      cmd.ExecuteNonQuery();    try    {     //执行存储过程杀掉其它进程     sql="exec  killspid 'mydb'";     cmd = new SqlCommand(sql, conn);     cmd.ExecuteNonQuery();    }        finally    {                         FileInfo backupdata = new FileInfo(nowpath+"//数据备份文件(不要删除).dll");     if (backupdata.Exists)     {

     sql="restore database mydb from disk='"+nowpath+"//数据备份文件(不要删除).dll"+"' with recovery";      cmd = new SqlCommand(sql, conn);      cmd.ExecuteNonQuery();      MessageBox.Show("成功恢复数据！从"+nowpath+"//数据备份文件(不要删除).dll 中恢复"," 成功了！", MessageBoxButtons.OK, MessageBoxIcon.Information );     }     else     {     MessageBox.Show("你可能以前没有备份过数据库！数据备份文件(不要删除).dll 不存在！"," 错误！", MessageBoxButtons.OK, MessageBoxIcon.Information );     }    }         }    catch(Exception err)    {     MessageBox.Show("出现错误，请对数据库端进行正确配置！"+err.ToString() ,"出错了!",MessageBoxButtons.OK ,MessageBoxIcon.Question  );          }    finally    {     conn.Close();     this.Close();         }

/************************************************************************************************/ //可参考下面代码。代码有待修改的地方，使用sql语句进行数据备份，恢复 /// <summary>         /// 备份数据库         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>   private void _ButtonBackUp_Click(object sender, System.EventArgs e)   {    dbConnSet frm = new dbConnSet();//获得数据库sa帐号，密码    frm.ShowDialog();    if(frm.isNormal==false)     return;    this.Cursor = Cursors.WaitCursor;    string _DBSource  = "(local)";    string _DBCatalog = "master";    string _DBUser    = frm.textBoxDBUser.Text;    string _DBPass    = frm.textBoxDBPas.Text;    string m_ConnectionStr   = "Provider=SQLOLEDB;Password="+_DBPass+";User ID="+_DBUser+";Initial Catalog="+_DBCatalog+";Data Source="+_DBSource+";Connect Timeout=5;";      OleDbConnection _Conn = new OleDbConnection(m_ConnectionStr);               try    {     _Conn.Open();     NetBee.Controls.FolderBrowserEX.FolderBrowser _SaveFileDialog = new NetBee.Controls.FolderBrowserEX.FolderBrowser();         //保存的文件目录路径           _SaveFileDialog.Description = "请选择备份保存的目录";     if(_SaveFileDialog.ShowDialog()==DialogResult.OK)     {             string _FolderPath = _SaveFileDialog.DirectoryPath;                   string[] DBName = {Cs.PubObject.dbName};      for(int i=0;i<DBName.Length;i++)      {                                  string _DBName   = DBName[i];       OleDbCommand _Comm = new OleDbCommand("",_Conn);       //执行数据库备份命令       _Comm.CommandText = "BACKUP DATABASE "+_DBName+" TO DISK = '"+ _SaveFileDialog.DirectoryPath+@"/"+_DBName +".bak'";       _Comm.ExecuteNonQuery();           }       this.Cursor = Cursors.Arrow;      _Conn.Close();      MessageBox.Show("备份数据成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);                   }                 this.Cursor = Cursors.Arrow;

   }    catch(System.Exception error)    {     this.Cursor = Cursors.Arrow;     MessageBox.Show("异常:"+error.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Error);    }    this.Cursor = Cursors.Arrow;   }

  #endregion

  #region 恢复数据库   /// <summary>   /// 恢复数据库   /// </summary>   /// <param name="sender"></param>   /// <param name="e"></param>   private void _ButtonRestory_Click(object sender, System.EventArgs e)   {    dbConnSet frm = new dbConnSet();    frm.ShowDialog();    if(frm.isNormal==false)     return;    if(MessageBox.Show("该操作将数据覆盖!!/n/n如果选择[是],将原来的数据覆盖/n/n如果选择[否],将退出安装","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question)==DialogResult.No)     return;    if(MessageBox.Show("请在此确认,该操作不能恢复!!/n/n如果选择[是],将原来的数据覆盖/n/n如果选择[否],将退出安装","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Warning)==DialogResult.No)     return;    this.Cursor = Cursors.WaitCursor;    string _DBSource  = "(local)";    string _DBCatalog = "master";    string _DBUser    = frm.textBoxDBUser.Text;    string _DBPass    = frm.textBoxDBPas.Text;    string m_ConnectionStr   = "Provider=SQLOLEDB;Password="+_DBPass+";User ID="+_DBUser+";Initial Catalog="+_DBCatalog+";Data Source="+_DBSource+";Connect Timeout=5;";     OleDbConnection _Conn = new OleDbConnection(m_ConnectionStr);               try    {     _Conn.Open();      NetBee.Controls.FolderBrowserEX.FolderBrowser _SaveFileDialog = new NetBee.Controls.FolderBrowserEX.FolderBrowser();                    _SaveFileDialog.Description = "请要恢复的数据目录";     if(_SaveFileDialog.ShowDialog()==DialogResult.OK)     {             string _FolderPath = _SaveFileDialog.DirectoryPath;                   string[] DBName = {Cs.PubObject.dbName};      for(int i=0;i<DBName.Length;i++)      {       string _DBName   = DBName[i];         string _BakFilePath = _SaveFileDialog.DirectoryPath+@"/"+DBName[i]+@".bak";       OleDbCommand _Comm = new OleDbCommand("restore filelistonly from disk='"获得原来的逻辑名称以及物理路径       OleDbDataReader _Reader = null;       _Reader = _Comm.ExecuteReader();       string[]  _LogName = new string[2];//获得逻辑名称       string[]  _PhiPath = new string[2];//获得物理路径       int j = 0;       while(_Reader.Read())       {        _LogName[j] = (string)_Reader.GetValue(0);        _PhiPath[j] = (string)_Reader.GetValue(1);        j = j+1;       }       _Reader.Close();              //执行数据库恢复脚本       _Comm = new OleDbCommand("",_Conn);       _Comm.CommandText ="RESTORE DATABASE [" + _DBName + "]   FROM DISK = '" + _BakFilePath + "' With Move '"+ _LogName[0] +        "' TO '" +_PhiPath[0]+ "',  Move '" + _LogName[1] + "' TO '" + _PhiPath[1]+ "'";       _Comm.ExecuteNonQuery();                  }        this.Cursor = Cursors.Arrow;      MessageBox.Show("数据恢复成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);      _Conn.Close();             }                 this.Cursor = Cursors.Arrow;

   }    catch(System.Exception error)    {     this.Cursor = Cursors.Arrow;     MessageBox.Show("异常:"+error.Message,"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);    }       }   #endregion

 

/******************************************************sqldmo

  private void Form1_Load(object sender, System.EventArgs e)   {    try    {     //get all available SQL Servers         sqlServers = sqlApp.ListAvailableSQLServers();     for(int i=0;i<sqlServers.Count;i++)     {      object srv = sqlServers.Item( i + 1);      if(srv != null)      {       this.cboServers.Items.Add(srv);            }     }     if(this.cboServers.Items.Count > 0)      this.cboServers.SelectedIndex = 0;     else      this.cboServers.Text = "<No available SQL Servers>";

   }    catch(Exception err)    {     MessageBox.Show(err.Message,"Error");    }   }

  //Get Tables   private void linkLabel2_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)   {    try    {     this.Cursor = Cursors.WaitCursor;     this.lstObjects.Items.Clear();     this.cboDatabase.Items.Clear();             SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();         srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);

    foreach(SQLDMO.Database db in srv.Databases)     {      if(db.Name!=null)       this.cboDatabase.Items.Add(db.Name);     }     this.cboDatabase.Sorted = true;     if(this.cboDatabase.Items.Count >0)     {      this.cboDatabase.SelectedIndex = 0;      this.cboDatabase.Enabled = true;      this.groupBox1.Enabled = true;     }     else     {      this.groupBox1.Enabled = false;      this.cboDatabase.Enabled = false;      this.cboDatabase.Text = "<No databases found>";     }     this.Cursor = Cursors.Default;    }    catch(Exception err)    {     this.Cursor = Cursors.Default;     MessageBox.Show(err.Message,"Error");    }   }   

  //Get stored procedures   private void linkLabel1_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)   {    this.Cursor = Cursors.WaitCursor;    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();        srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);    for(int i=0;i<srv.Databases.Count;i++)    {     if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())     {      SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");      this.lstObjects.Items.Clear();      for(int j=0;j<db.Tables.Count;j++)      {       this.lstObjects.Items.Add(db.Tables.Item(j+1,"dbo").Name);      }      this.Cursor = Cursors.Default;      return;     }    }    this.Cursor = Cursors.Default;   }      // Get Views   private void linkLabel3_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)   {    this.Cursor = Cursors.WaitCursor;    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();        srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);    for(int i=0;i<srv.Databases.Count;i++)    {     if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())     {      SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");      this.lstObjects.Items.Clear();            for(int j=0;j<db.StoredProcedures.Count;j++)      {       this.lstObjects.Items.Add(db.StoredProcedures.Item(j+1,"dbo").Name);      }      this.Cursor = Cursors.Default;      return;     }    }    this.Cursor = Cursors.Default;   }

  private void linkLabel4_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)   {    this.Cursor = Cursors.WaitCursor;    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();        srv.Connect(this.cboServers.SelectedText,this.txtUser.Text,this.txtPassword.Text);    for(int i=0;i<srv.Databases.Count;i++)    {     if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())     {      SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");      this.lstObjects.Items.Clear();      for(int j=0;j<db.Views.Count;j++)      {       this.lstObjects.Items.Add(db.Views.Item(j+1,"dbo").Name);      }      this.Cursor = Cursors.Default;      return;     }    }    this.Cursor = Cursors.Default;   }

  private void cboDatabase_SelectedIndexChanged(object sender, System.EventArgs e)   {    this.lstObjects.Items.Clear();   }

    }