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 }
