ASP.NET SQLServer 2005データベースのバックアップとリストア
19029 ワード
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 }