数据库备份

using    System;   
using    System.Data;   
using    System.Data.SqlClient;   
    
   namespace    bachupsqlserver   
   {   
          /**////   <summary>   
          ///   备份和还原sql   server   2000数据库,在asp.net中文正式版和sql   server   2000系统上通过   
          ///   </summary>   
          public   class   BackupData   
          {   
                  private   SqlConnection   conn;   
                  public   BackupData()   
                  {   
                          //   
                          //   TODO:   在此处添加构造函数逻辑   
                          //   
                          string   sql="data   source=localhost;initial   catalog=master;password=;persist   security   info=True;user   id=sa;workstation   id=TOPS03496;packet   size=4096";//注意默认数据库不要和恢复的数据库同名   
    
                          init(sql);   
                  }   
    
                  /**////   <summary>   
                  ///   备份数据库   
                  ///   </summary>   
                  ///   <param   name="databasename">要备份的数据源名称</param>   
                  ///   <param   name="backuptodatabase">备份到的数据库文件名称及路径</param>   
                  ///   <returns></returns>   
                  public   bool   BackUpDataBase(string   databasename,string   backuptodatabase)   
                  {   
                          string   procname;   
                          string   name=databasename+DateTime.Now.Year.ToString()+DateTime.Now.Month.ToString()+DateTime.Now.Date.Day.ToString()+DateTime.Now.Minute.ToString();   
                          string   sql;   
    
                          conn.Open();                 //打开数据库连接   
    
                          //删除逻辑备份设备，但不会删掉备份的数据库文件   
                          procname="sp_dropdevice";   
                          SqlCommand   sqlcmd1=new   SqlCommand(procname,conn);   
                          sqlcmd1.CommandType   =CommandType.StoredProcedure;   
    
                          SqlParameter   sqlpar=new   SqlParameter();   
                          sqlpar=sqlcmd1.Parameters.Add("@logicalname",SqlDbType.VarChar,20);   
                          sqlpar.Direction   =ParameterDirection.Input;   
                          sqlpar.Value   =databasename;   
    
                          try                 //如果逻辑设备不存在，略去错误   
                          {   
                                  sqlcmd1.ExecuteNonQuery();   
                          }   
                          catch   
                          {   
                          }   
    
                          //创建逻辑备份设备   
                          procname="sp_addumpdevice";   
                          SqlCommand   sqlcmd2=new   SqlCommand(procname,conn);   
                          sqlcmd2.CommandType   =CommandType.StoredProcedure;   
    
                          sqlpar=sqlcmd2.Parameters.Add("@devtype",SqlDbType.VarChar,20);   
                          sqlpar.Direction   =ParameterDirection.Input;   
                          sqlpar.Value   ="disk";   
    
                            
                          sqlpar=sqlcmd2.Parameters.Add("@logicalname",SqlDbType.VarChar,20);//逻辑设备名   
                          sqlpar.Direction   =ParameterDirection.Input;   
                          sqlpar.Value   =databasename;   
    
                          sqlpar=sqlcmd2.Parameters.Add("@physicalname",SqlDbType.NVarChar,260);//物理设备名   
                          sqlpar.Direction   =ParameterDirection.Input;   
                          sqlpar.Value   =backuptodatabase+name+".bak";   
    
                            
                          try   
                          {   
                                  int   i=sqlcmd2.ExecuteNonQuery();   
                          }   
                          catch(Exception   err)   
                          {   
                                  string   str=err.Message;   
                          }   
    
                          //备份数据库到指定的数据库文件(完全备份)   
                          sql="BACKUP   DATABASE   "+databasename   +"   TO   "+databasename   +"   WITH   INIT";   
                          SqlCommand   sqlcmd3=new   SqlCommand(sql,conn);   
                          sqlcmd3.CommandType   =CommandType.Text;   
                          try   
                          {   
                                  sqlcmd3.ExecuteNonQuery();   
                          }   
                          catch(Exception   err)   
                          {   
                                  string   str=err.Message   ;   
                                  conn.Close();   
    
                                  return   false;   
                          }   
    
                          conn.Close();//关闭数据库连接   
                          return   true;   
    
                  }   
    
                  /**////   <summary>   
                  ///   还原指定的数据库文件   
                  ///   </summary>   
                  ///   <param   name="databasename">要还原的数据库</param>   
                  ///   <param   name="databasefile">数据库备份文件及路径</param>   
                  ///   <returns></returns>   
                  public   bool   RestoreDataBase(string   databasename,string   databasefile   )   
                  {   
    
                          //还原指定的数据库文件   
                          string   sql="RESTORE   DATABASE   "+databasename   +"   from   DISK   =   '"+databasefile   +"'   ";   
                          SqlCommand   sqlcmd=new   SqlCommand(sql,conn);   
                          sqlcmd.CommandType   =CommandType.Text;   
    
                          conn.Open();   
    
                          try   
                          {   
                                  sqlcmd.ExecuteNonQuery();   
                          }   
                          catch(Exception   err)   
                          {   
                                  string   str=err.Message   ;   
                                  conn.Close();   
    
                                  return   false;   
                          }   
    
                          conn.Close();//关闭数据库连接   
                          return   true;   
                  }   
    
                  /**////   <summary>   
                  ///   初始化数据库的连接   
                  ///   </summary>   
                  ///   <param   name="strconn"></param>   
                  private   void   init(string   strconn)   
                  {   
                          conn=new   SqlConnection(strconn);   
    
                  }   
          }   
  }    

参见在C#中运用SQLDMO备份和恢复Microsoft   SQL   Server数据库  
  http://dev.csdn.net/develop/article/28/28564.shtm  
  当不使用要恢复的数据库时以上方法可行，但当你使用了数据库时就必须杀死该进程  
  代码如下：  
   
  ///   <summary>  
  ///   还原数据库函数  
  ///   </summary>  
  ///   <param   name="strDbName">数据库名</param>  
  ///   <param   name="strFileName">数据库备份文件的完整路径名</param>  
  ///   <returns></returns>  
  public   bool   RestoreDB(string   strDbName,string   strFileName)    
  {    
  //PBar   =   pgbMain   ;    
  SQLDMO.SQLServer   svr   =   new   SQLDMO.SQLServerClass()   ;    
  try    
  {    
  //服务器名，数据库用户名，数据库用户名密码  
  svr.Connect("localhost","sa","hai")   ;  
   
  SQLDMO.QueryResults   qr   =   svr.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   ;    
  }    
                                  //杀死使用strDbName数据库的进程  
  for(int   i=1;i<=qr.Rows;i++)    
  {    
  int   lPID   =   qr.GetColumnLong(i,iColPIDNum)   ;    
  string   strDBName   =   qr.GetColumnString(i,iColDbName)   ;    
  if   (strDBName.ToUpper()   ==   strDbName.ToUpper())    
  {  
  svr.KillProcess(lPID)   ;    
  }  
  }    
                                   
  SQLDMO.Restore   res   =   new   SQLDMO.RestoreClass()   ;    
  res.Action   =   0   ;    
  res.Files   =   strFileName   ;    
   
  res.Database   =   strDbName   ;    
  res.ReplaceDatabase   =   true   ;    
  res.SQLRestore(svr)   ;    
  return   true   ;    
  }    
  catch  
  {    
  return   false;  
  }    
  finally    
  {    
  svr.DisConnect()   ;    
  }    
  }  

转载于:https://www.cnblogs.com/oflying907/archive/2006/05/19/404102.html