asp.net/C# 备份与还原SqlServer数据库

asp.net/C# 备份与还原数据库

功能:
   自动识别Web.Config配置信息.
   备份与还原数据库.
代码如下:
  1. using System;
  2. using System.Configuration;
  3. using System.Data.SqlClient;
  4. using System.Data;
  5. namespace OA.Web
  6. {
  7.     /// <summary> 
  8.     /// SqlDbBackUpRestore类,主要应用SQLDMO实现对Microsoft SQL Server数据库 的备份和恢复 
  9.     /// 请先给项目添加COM(Mircrosoft SQLDMO Object Library)引用
  10.     /// </summary> 
  11.     public class SqlDbBackUpRestore
  12.     {
  13.         /// <summary>
  14.         /// sql服务器名称或者IP地址
  15.         /// </summary>
  16.         private string server;
  17.         /// <summary>
  18.         ///用户名称
  19.         /// </summary>
  20.         private string uid;
  21.         /// <summary>
  22.         /// 用户密码
  23.         /// </summary>
  24.         private string pwd;
  25.         /// <summary>
  26.         /// 数据库名称
  27.         /// </summary>
  28.         private string database;
  29.         /// <summary>
  30.         /// Web.Config连接字符串
  31.         /// </summary>
  32.         private string conn;
  33.         /// <summary> 
  34.         /// SqlDbBackUpRestore类的构造函数 
  35.         /// </summary> 
  36.         public SqlDbBackUpRestore()
  37.         {
  38.             conn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"].ToString();
  39.             server = cut(conn, "server="";");
  40.             uid = cut(conn, "uid="";");
  41.             pwd = cut(conn, "pwd="";");
  42.             database = cut(conn, "database="";");
  43.         }
  44.         public string cut(string str, string bg, string ed)
  45.         {
  46.             string sub;
  47.             sub = str.Substring(str.IndexOf(bg) + bg.Length);
  48.             sub = sub.Substring(0, sub.IndexOf(";"));
  49.             return sub;
  50.         }
  51.         /// <summary> 
  52.         /// 数据库database备份 
  53.         /// </summary> 
  54.         public bool DbBackup(string url)
  55.         {
  56.             SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
  57.             SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
  58.             try
  59.             {
  60.                 oSQLServer.LoginSecure = false;
  61.                 oSQLServer.Connect(server, uid, pwd);
  62.                 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
  63.                 oBackup.Database = database;
  64.                 oBackup.Files = url;//"d:/Northwind.bak"; 
  65.                 oBackup.BackupSetName = database;
  66.                 oBackup.BackupSetDescription = "数据库备份";
  67.                 oBackup.Initialize = true;
  68.                 oBackup.SQLBackup(oSQLServer);
  69.                 return true;
  70.             }
  71.             catch
  72.             {
  73.                 return false;
  74.                 throw;
  75.             }
  76.             finally
  77.             {
  78.                 oSQLServer.DisConnect();
  79.             }
  80.         }
  81.         /// <summary> 
  82.         /// 数据库database恢复 
  83.         /// </summary> 
  84.         public string DbRestore(string url)
  85.         {
  86.             if (KillProcesses() != true)//执行存储过程 
  87.             {
  88.                 return "操作失败";
  89.             }
  90.             else
  91.             {
  92.                 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
  93.                 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
  94.                 try
  95.                 {
  96.                     oSQLServer.LoginSecure = false;
  97.                     oSQLServer.Connect(server, uid, pwd);
  98.                     oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
  99.                     oRestore.Database = database;
  100.                     oRestore.Files = url;//@"d:/Northwind.bak"; 
  101.                     oRestore.FileNumber = 1;
  102.                     oRestore.ReplaceDatabase = true;
  103.                     oRestore.SQLRestore(oSQLServer);
  104.                     return "OK";
  105.                 }
  106.                 catch (Exception e)
  107.                 {
  108.                     return "恢复数据库失败";
  109.                     throw;
  110.                 }
  111.                 finally
  112.                 {
  113.                     oSQLServer.DisConnect();
  114.                 }
  115.             }
  116.         }
  117.         /// <summary>
  118.         /// 杀死Sql database 连接进程
  119.         /// </summary>
  120.         /// <returns></returns>
  121.         private bool KillProcesses()
  122.         {
  123.             SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
  124.             try
  125.             {
  126.                 svr.Connect(server, uid, pwd);
  127.                 SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
  128.                 int iColPIDNum = -1;
  129.                 int iColDbName = -1;
  130.                 for (int i = 1; i <= qr.Columns; i++)
  131.                 {
  132.                     string strName = qr.get_ColumnName(i);
  133.                     if (strName.ToUpper().Trim() == "SPID")
  134.                     {
  135.                         iColPIDNum = i;
  136.                     }
  137.                     else if (strName.ToUpper().Trim() == "DBNAME")
  138.                     {
  139.                         iColDbName = i;
  140.                     }
  141.                     if (iColPIDNum != -1 && iColDbName != -1)
  142.                         break;
  143.                 }
  144.                 for (int i = 1; i <= qr.Rows; i++)
  145.                 {
  146.                     int lPID = qr.GetColumnLong(i, iColPIDNum);
  147.                     string strDBName = qr.GetColumnString(i, iColDbName);
  148.                     if (strDBName.ToUpper() == database.ToUpper())
  149.                     {
  150.                         svr.KillProcess(lPID);
  151.                     }
  152.                 }
  153.                 return true;
  154.             }
  155.             catch
  156.             {
  157.                 //失败
  158.                 return false;
  159.             }
  160.             finally
  161.             {
  162.                 svr.DisConnect();
  163.             }
  164.         }
  165.     }
  166. }

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页