SQLDMO 备份还原数据库

 

#region 备份数据库

          /// <summary>
          /// 数据库的备份和实时进度显示 
          /// </summary>
          /// <param name="strDbName"></param>
          /// <param name="strFileName"></param>
          /// <param name="pgbMain"></param>
          /// <returns></returns>
          public bool BackUPDB(string strDbName, string strFileName)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  SQLDMO.Backup bak = new SQLDMO.BackupClass();
                  bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                  bak.Initialize = true;
                  bak.Files = strFileName;
                  bak.Database = strDbName;
                  bak.SQLBackup(svr);
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("备份数据库失败" + err.Message));
                  ShowError("连接数据库出错：" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 还原数据库
          /// <summary>
          /// 恢复数据库，恢复前杀死所有与本数据库相关进程
          /// </summary>
          /// <param name="strDbName">数据库名</param>
          /// <param name="strFileName">存放路径</param>
          /// <param name="pgbMain"></param>
          /// <returns></returns>
          public bool RestoreDB(string strDbName, string strFileName)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  //取得所有的进程列表
                  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;
                  }
                  //将相关进程杀死
                  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 = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                  res.Files = strFileName;

                  res.Database = strDbName;
                  res.FileNumber = 1;
                
                  res.ReplaceDatabase = true;
                  res.SQLRestore(svr);
                
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("" + err.Message));
                  ShowError("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 附加数据库
          public bool AttachDB(string dbName, string dbFile)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  svr.AttachDB(dbName, dbFile);
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("附加数据库失败" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 创建库

          public bool CreateDB(string dbName, string path)
          {
              // 创建数据库文件
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  dbFile.Name = dbName + "_Data";
                  dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
                  dbFile.PrimaryFile = true;
                  //dbFile.Size = 2; // 设置初始化大小(MB)
                  //dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // 设置文件增长方式
                  //dbFile.FileGrowth=1; // 设置增长幅度

                  // 创建日志文件
                  SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
                  logFile.Name = dbName + "_Log";
                  logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
                  //logFile.Size = 3;
                  //logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
                  //logFile.FileGrowth=1;

                  // 创建数据库
                  SQLDMO.Database db = new SQLDMO.DatabaseClass();
                  db.Name = dbName;
                  db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
                  db.TransactionLog.LogFiles.Add(logFile);

                  // 建立数据库联接，并添加数据库到服务器
                  svr.Databases.Add(db);
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("添加数据库失败!" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 删除数据库
          public bool KillDB(string dbName)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  svr.KillDatabase(dbName);
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("删除数据库失败!" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 删除表

          public bool KillTable(string DataBaseName,string tbName)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();    
              try
              {
                
                  svr.Connect(ServerName, UserName, Password);
                  svr.Databases.Item(DataBaseName, "owner").Tables.Remove(tbName, "owner");
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("删除表失败!" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 删除存储过程
          public bool KillStored(string DataBaseName, string tbName)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {

                  svr.Connect(ServerName, UserName, Password);
                  svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Remove(tbName, "owner");
                  return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("删除存储过程失败!" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 添加存储过程
          public bool UpdateStored(string DataBaseName, string StoredName,string StoredText)
          {
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {

                  svr.Connect(ServerName, UserName, Password);
                  if (StoredName == "")
                  {
                      SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
                      spd.Text = StoredText;
                      svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Add(spd);
                  }
                  else
                  {
                      SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
                      SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
                      dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
                      foreach (SQLDMO.StoredProcedure sp in dbs.StoredProcedures)
                      {
                          if (sp.Name == StoredName)
                              spd = sp;
                      }
                      spd.Alter(StoredText);
                   }
                      return true;
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("修改存储过程失败!" + err.Message);
                  return false;
              }
              finally
              {
                  svr.DisConnect();
              }
          }
          #endregion


          #region 返回存储过程
          public string GetStoredText(string DataBaseName, string StoredName)
          {
              string getStoredText = "";
              SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
              try
              {
                  svr.Connect(ServerName, UserName, Password);
                  SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
                  dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");

                  foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
                  {
                      if (tb.Name == StoredName)
                          getStoredText= tb.Text;
                  }
              }
              catch (Exception err)
              {
                  //throw (new Exception("！" + err.Message));
                  ShowError("修改存储过程失败!" + err.Message);
                  getStoredText= "";
              }
              finally
              {
                  svr.DisConnect();
              }
              return getStoredText;
          }
          #endregion