利用SQLDMO实现的,只要添加SQLDMO引用就好了,然后利用下边的类的方法就可以实现了。
我把原作者的类扩充了一下,可以自动识别web.config里 的数据库连接字符串,可以通过变量设置还原恢复的信息。
需要注意的是还原,还原的时候问题最大了,有别的用户使用数据库的时候无法还原,解决办法就是在MASTER数据库中添加一个存储过程:
Code
1create proc killspid (@dbname varchar(20))
2as
3begin
4declare @sql nvarchar(500)
5declare @spid int
6set @sql='declare getspid cursor for
7select spid from sysprocesses where dbid=db_id(mailto:%20%20%20+@dbname+%20%20%20)
8exec (@sql)
9open getspid
10fetch next from getspid into @spid
11while @@fetch_status<>-1
12begin
13exec('kill mailto:%20+@spid)
14fetch next from getspid into @spid
15end
16close getspid
17deallocate getspid
18end
19GO
1create proc killspid (@dbname varchar(20))
2as
3begin
4declare @sql nvarchar(500)
5declare @spid int
6set @sql='declare getspid cursor for
7select spid from sysprocesses where dbid=db_id(mailto:%20%20%20+@dbname+%20%20%20)
8exec (@sql)
9open getspid
10fetch next from getspid into @spid
11while @@fetch_status<>-1
12begin
13exec('kill mailto:%20+@spid)
14fetch next from getspid into @spid
15end
16close getspid
17deallocate getspid
18end
19GO
******************************************************************************************************************/
在还原之前先执行这个存储过程,需要传递dbname,就是你的数据库的名字。下边是类的原代码:(web.config里的数据库连接字符串是constr)
Code
1using System;
2
3using System.Configuration;
4
5using System.Data.SqlClient;
6
7using System.Data;
8
9namespace web.base_class
10
11{
12
13 /**//**//**//// <summary>
14
15 /// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复
16
17 /// </summary>
18
19 public class DbOper
20
21 {
22
23 private string server;
24
25 private string uid;
26
27 private string pwd;
28
29 private string database;
30
31 private string conn;
32
33 /**//**//**//// <summary>
34
35 /// DbOper类的构造函数
36
37 /// </summary>
38
39 public DbOper()
40
41 {
42
43 conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
44
45 server=cut(conn,"server=",";");
46
47 uid=cut(conn,"uid=",";");
48
49 pwd=cut(conn,"pwd=",";");
50
51 database=cut(conn,"database=",";");
52
53 }
54
55 public string cut(string str,string bg,string ed)
56
57 {
58
59 string sub;
60
61 sub=str.Substring(str.IndexOf(bg)+bg.Length);
62
63 sub=sub.Substring(0,sub.IndexOf(";"));
64
65 return sub;
66
67 }
68
69
70
71 /**//**//**//// <summary>
72
73 /// 数据库备份
74
75 /// </summary>
76
77 public bool DbBackup(string url)
78
79 {
80
81 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
82
83 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
84
85 try
86
87 {
88
89 oSQLServer.LoginSecure = false;
90
91 oSQLServer.Connect(server,uid, pwd);
92
93 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
94
95 oBackup.Database = database;
96
97 oBackup.Files = url;//"d:\Northwind.bak";
98
99 oBackup.BackupSetName = database;
100
101 oBackup.BackupSetDescription = "数据库备份";
102
103 oBackup.Initialize = true;
104
105 oBackup.SQLBackup(oSQLServer);
106
107 return true;
108
109 }
110
111 catch
112
113 {
114
115 return false;
116
117 throw;
118
119 }
120
121 finally
122
123 {
124
125 oSQLServer.DisConnect();
126
127 }
128
129 }
130
131
132
133 /**//**//**//// <summary>
134
135 /// 数据库恢复
136
137 /// </summary>
138
139 public string DbRestore(string url)
140
141 {
142
143 if(exepro()!=true)//执行存储过程
144
145 {
146
147 return "操作失败";
148
149 }
150
151 else
152
153 {
154
155 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
156
157 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
158
159 try
160
161 {
162
163 oSQLServer.LoginSecure = false;
164
165 oSQLServer.Connect(server, uid, pwd);
166
167 oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
168
169 oRestore.Database = database;
170
171 oRestore.Files = url;//@"d:\Northwind.bak";
172
173 oRestore.FileNumber = 1;
174
175 oRestore.ReplaceDatabase = true;
176
177 oRestore.SQLRestore(oSQLServer);
178
179 return "ok";
180
181 }
182
183 catch(Exception e)
184
185 {
186
187 return "恢复数据库失败";
188
189 throw;
190
191 }
192
193 finally
194
195 {
196
197 oSQLServer.DisConnect();
198
199 }
200
201 }
202
203 }
204
205 private bool exepro()
206
207 {
208
209 SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
210
211 SqlCommand cmd = new SqlCommand("killspid",conn1);
212
213 cmd.CommandType = CommandType.StoredProcedure;
214
215 cmd.Parameters.Add("@dbname","port");
216
217 try
218
219 {
220
221 conn1.Open();
222
223 cmd.ExecuteNonQuery();
224
225 return true;
226
227 }
228
229 catch(Exception ex)
230
231 {
232
233 return false;
234
235 }
236
237 finally
238
239 {
240
241 conn1.Close();
242
243 }
244
245
246
247 }
248
249 }
250
251}
252
253
1using System;
2
3using System.Configuration;
4
5using System.Data.SqlClient;
6
7using System.Data;
8
9namespace web.base_class
10
11{
12
13 /**//**//**//// <summary>
14
15 /// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复
16
17 /// </summary>
18
19 public class DbOper
20
21 {
22
23 private string server;
24
25 private string uid;
26
27 private string pwd;
28
29 private string database;
30
31 private string conn;
32
33 /**//**//**//// <summary>
34
35 /// DbOper类的构造函数
36
37 /// </summary>
38
39 public DbOper()
40
41 {
42
43 conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
44
45 server=cut(conn,"server=",";");
46
47 uid=cut(conn,"uid=",";");
48
49 pwd=cut(conn,"pwd=",";");
50
51 database=cut(conn,"database=",";");
52
53 }
54
55 public string cut(string str,string bg,string ed)
56
57 {
58
59 string sub;
60
61 sub=str.Substring(str.IndexOf(bg)+bg.Length);
62
63 sub=sub.Substring(0,sub.IndexOf(";"));
64
65 return sub;
66
67 }
68
69
70
71 /**//**//**//// <summary>
72
73 /// 数据库备份
74
75 /// </summary>
76
77 public bool DbBackup(string url)
78
79 {
80
81 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
82
83 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
84
85 try
86
87 {
88
89 oSQLServer.LoginSecure = false;
90
91 oSQLServer.Connect(server,uid, pwd);
92
93 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
94
95 oBackup.Database = database;
96
97 oBackup.Files = url;//"d:\Northwind.bak";
98
99 oBackup.BackupSetName = database;
100
101 oBackup.BackupSetDescription = "数据库备份";
102
103 oBackup.Initialize = true;
104
105 oBackup.SQLBackup(oSQLServer);
106
107 return true;
108
109 }
110
111 catch
112
113 {
114
115 return false;
116
117 throw;
118
119 }
120
121 finally
122
123 {
124
125 oSQLServer.DisConnect();
126
127 }
128
129 }
130
131
132
133 /**//**//**//// <summary>
134
135 /// 数据库恢复
136
137 /// </summary>
138
139 public string DbRestore(string url)
140
141 {
142
143 if(exepro()!=true)//执行存储过程
144
145 {
146
147 return "操作失败";
148
149 }
150
151 else
152
153 {
154
155 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
156
157 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
158
159 try
160
161 {
162
163 oSQLServer.LoginSecure = false;
164
165 oSQLServer.Connect(server, uid, pwd);
166
167 oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
168
169 oRestore.Database = database;
170
171 oRestore.Files = url;//@"d:\Northwind.bak";
172
173 oRestore.FileNumber = 1;
174
175 oRestore.ReplaceDatabase = true;
176
177 oRestore.SQLRestore(oSQLServer);
178
179 return "ok";
180
181 }
182
183 catch(Exception e)
184
185 {
186
187 return "恢复数据库失败";
188
189 throw;
190
191 }
192
193 finally
194
195 {
196
197 oSQLServer.DisConnect();
198
199 }
200
201 }
202
203 }
204
205 private bool exepro()
206
207 {
208
209 SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
210
211 SqlCommand cmd = new SqlCommand("killspid",conn1);
212
213 cmd.CommandType = CommandType.StoredProcedure;
214
215 cmd.Parameters.Add("@dbname","port");
216
217 try
218
219 {
220
221 conn1.Open();
222
223 cmd.ExecuteNonQuery();
224
225 return true;
226
227 }
228
229 catch(Exception ex)
230
231 {
232
233 return false;
234
235 }
236
237 finally
238
239 {
240
241 conn1.Close();
242
243 }
244
245
246
247 }
248
249 }
250
251}
252
253