ASP.NET SQLServer2005 数据库备份与还原

  1 using System;
  2 using System.Data.SqlClient;
  3 using System.Data;
  4 using System.IO;
  5 
  6 namespace NET.Common
  7 {
  8     /// <summary>
  9     /// 数据库操作帮助类
 10     /// </summary>
 11     public class DatabaseHelper
 12     {
 13         private const string SQL_DATABASE_BACK = "BACKUP DATABASE {0} TO DISK = '{1}'"; //数据库备份语句
 14         private const string SQL_DATABASE_RESTORE = "RESTORE DATABASE {0} FROM DISK = '{1}' WITH REPLACE"; //数据库还原语句
 15         private const string SQL_SELECT_ALL_CONN = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('{0}')"; //获取指定数据库所有链接语句
 16 
 17         /// <summary>
 18         /// 实例数据库操作
 19         /// </summary>
 20         /// <param name="masterConnectionString">master数据库链接字符串</param>
 21         public DatabaseHelper(string masterConnectionString)
 22         {
 23             this.MasterConnectionString = masterConnectionString;
 24         }
 25 
 26         /// <summary>
 27         /// master数据库链接字符串
 28         /// </summary>
 29         private string MasterConnectionString { get; set; }
 30 
 31         /// <summary>
 32         /// SQLServer数据库备份
 33         /// </summary>
 34         /// <param name="connectionString">数据库连接字符串</param>
 35         /// <param name="backName">备份文件名称，不包含后缀名</param>
 36         /// <param name="path">备份文件存放的物理路径</param>
 37         public bool Back(string dbName, string backName, string path)
 38         {
 39             //判断存放备份文件的目录是否存在
 40             if (!Directory.Exists(path))
 41             {
 42                 //不存在，新建目录
 43                 Directory.CreateDirectory(path);
 44             }
 45 
 46             try
 47             {
 48                 using (SqlConnection conn = new SqlConnection(MasterConnectionString))
 49                 {
 50                     //设置存放备份文件的完整物理路径
 51                     string backPath = path + "\\" + backName + ".bak";
 52 
 53                     //设置数据库备份的T-SQL语句
 54                     string t_sql_back = string.Format(SQL_DATABASE_BACK, dbName, backPath);
 55 
 56                     using (SqlCommand cmd = new SqlCommand(t_sql_back, conn))
 57                     {
 58                         cmd.CommandType = CommandType.Text;
 59                         conn.Open();
 60 
 61                         //开始备份
 62                         cmd.ExecuteNonQuery();
 63 
 64                         return true;
 65                     }
 66                 }
 67             }
 68             catch
 69             {
 70                 return false;
 71             }
 72         }
 73 
 74         /// <summary>
 75         /// SQLServer数据库还原
 76         /// </summary>
 77         /// <param name="dbName">要进行还原的数据库名称</param>
 78         /// <param name="fullPath">备份文件的完整物理路径</param>
 79         public bool Restore(string dbName, string fullPath)
 80         {
 81             //判断还原使用的文件是否存在
 82             if (!File.Exists(fullPath))
 83             {
 84                 return false;
 85             }
 86 
 87             try
 88             {
 89                 using (SqlConnection conn = new SqlConnection(MasterConnectionString))
 90                 {
 91                     //设置数据库还原的T-SQL语句
 92                     string t_sql_restore = string.Format(SQL_DATABASE_RESTORE, dbName, fullPath);
 93 
 94                     //设置查询指定数据库的所有连接
 95                     string t_sql_select_all_conn = string.Format(SQL_SELECT_ALL_CONN, dbName);
 96 
 97                     string t_sql_kill;
 98 
 99                     conn.Open();
100 
101                     //获取指定数据库所有连接
102                     SqlCommand cmd = new SqlCommand(t_sql_select_all_conn, conn);
103                     SqlDataAdapter reSDA = new SqlDataAdapter(cmd);
104                     DataTable dtConn = new DataTable();
105                     reSDA.Fill(dtConn);
106 
107                     //逐一删除连接
108                     for (int i = 0; i < dtConn.Rows.Count; i++)
109                     {
110                         t_sql_kill = " kill  " + dtConn.Rows[i][0].ToString();
111                         cmd = new SqlCommand(t_sql_kill, conn);
112                         cmd.ExecuteNonQuery();
113                     }
114 
115                     //执行还原操作
116                     cmd = new SqlCommand(t_sql_restore, conn);
117                     cmd.ExecuteNonQuery();
118 
119                     //清空当前连接池，这个很主要，如果不清空还原后第一次访问会出现“在向服务器发送请求时发生传输级错误。 (provider: 共享内存提供程序, error: 0 - 管道的另一端上无任何进程。)”错误
120                     SqlConnection.ClearPool(conn);
121                     return true;
122                 }
123 
124             }
125             catch
126             {
127                 return false;
128             }
129         }
130     }
131 }