如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
我们知道，用SQLDMO可以实现对数据库的备份与恢复，下面给出简单的实现方法。
首先需要添加对SQLDMO引用
1.实现数据库的备份：
2.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程
完整的操作类如下：
在相应的按钮
首先需要添加对SQLDMO引用
1.实现数据库的备份：
1
/**//// <summary>
2
/// 数据库备份
3 /// </summary>
4
/// <returns>备份是否成功</returns>
5
public bool DbBackup()
6 
{
7 string path = CreatePath();
8 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
9 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
10 try
11
{
12 oSQLServer.LoginSecure = false;
13 oSQLServer.Connect(server,uid, pwd);
14 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15 oBackup.Database = database;
16 oBackup.Files = path;
17 oBackup.BackupSetName = database;
18 oBackup.BackupSetDescription = "数据库备份";
19 oBackup.Initialize = true;
20 oBackup.SQLBackup(oSQLServer);
21
22 return true;
23
}
24 catch(Exception ex)
25 {
26 return false;
27 throw ex;
28 }
29 finally
30 {
31 oSQLServer.DisConnect();
32 }
33 }
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程
1/**//// <summary>
2 /// 数据库恢复
3 /// </summary>
4 public string DbRestore()
5 {
6 if(exepro()!=true)//执行存储过程
7 {
8 return "操作失败";
9 }
10 else
11 {
12 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
13 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
14 try
15 {
16 exepro();
17 oSQLServer.LoginSecure = false;
18 oSQLServer.Connect(server, uid, pwd);
19 oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
20 oRestore.Database = database;
21 /**////自行修改
22 oRestore.Files = @"d:\aaa\aaa.bak";
23 oRestore.FileNumber = 1;
24 oRestore.ReplaceDatabase = true;
25 oRestore.SQLRestore(oSQLServer);
26
27 return "ok";
28 }
29 catch(Exception e)
30 {
31 return "恢复数据库失败";
32 throw e;
33 }
34 finally
35 {
36 oSQLServer.DisConnect();
37 }
38 }
39 }
40
41 /**//// <summary>
42 /// 杀死当前库的所有进程
43 /// </summary>
44 /// <returns></returns>
45 private bool exepro()
46 {
47
48 SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
49 SqlCommand cmd = new SqlCommand("killspid",conn1);
50 cmd.CommandType = CommandType.StoredProcedure;
51 cmd.Parameters.Add("@dbname","aaa");
52 try
53 {
54 conn1.Open();
55 cmd.ExecuteNonQuery();
56 return true;
57 }
58 catch(Exception ex)
59 {
60 return false;
61 }
62 finally
63 {
64 conn1.Close();
65 }
66 }
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
完整的操作类如下：
1using System;
2using System.Collections;
3using System.Data;
4using System.Data.SqlClient;
5
6namespace DbBackUp
7{
8 /**//// <summary>
9 /// 创建人：Terrylee
10 /// 创建时间：2005年8月1日
11 /// 功能描述：实现数据库的备份和还原
12 /// 更新记录：
13 /// </summary>
14 public class DbOperate
15 {
16 /**//// <summary>
17 /// 服务器
18 /// </summary>
19 private string server;
20
21 /**//// <summary>
22 /// 登录名
23 /// </summary>
24 private string uid;
25
26 /**//// <summary>
27 /// 登录密码
28 /// </summary>
29 private string pwd;
30
31 /**//// <summary>
32 /// 要操作的数据库
33 /// </summary>
34 private string database;
35
36 /**//// <summary>
37 /// 数据库连接字符串
38 /// </summary>
39 private string conn;
40
41 /**//// <summary>
42 /// DbOperate类的构造函数
43 /// 在这里进行字符串的切割，获取服务器，登录名，密码，数据库
44 /// </summary>
45 public DbOperate()
46 {
47 conn = System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
48 server = StringCut(conn,"server=",";");
49 uid = StringCut(conn,"uid=",";");
50 pwd = StringCut(conn,"pwd=",";");
51 database = StringCut(conn,"database=",";");
52 }
53
54 /**//// <summary>
55 /// 切割字符串
56 /// </summary>
57 /// <param name="str"></param>
58 /// <param name="bg"></param>
59 /// <param name="ed"></param>
60 /// <returns></returns>
61 public string StringCut(string str,string bg,string ed)
62 {
63 string sub;
64 sub=str.Substring(str.IndexOf(bg)+bg.Length);
65 sub=sub.Substring(0,sub.IndexOf(";"));
66 return sub;
67 }
68
69 /**//// <summary>
70 /// 构造文件名
71 /// </summary>
72 /// <returns>文件名</returns>
73 private string CreatePath()
74 {
75 string CurrTime = System.DateTime.Now.ToString();
76 CurrTime = CurrTime.Replace("-","");
77 CurrTime = CurrTime.Replace(":","");
78 CurrTime = CurrTime.Replace(" ","");
79 CurrTime = CurrTime.Substring(0,12);
80 string path = @"d:\\aaa\\";
81 path += database;
82 path += "_db_";
83 path += CurrTime;
84 path += ".BAK";
85 return path;
86 }
87
88 /**//// <summary>
89 /// 数据库备份
90 /// </summary>
91 /// <returns>备份是否成功</returns>
92 public bool DbBackup()
93 {
94 string path = CreatePath();
95 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
96 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
97 try
98 {
99 oSQLServer.LoginSecure = false;
100 oSQLServer.Connect(server,uid, pwd);
101 oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
102 oBackup.Database = database;
103 oBackup.Files = path;
104 oBackup.BackupSetName = database;
105 oBackup.BackupSetDescription = "数据库备份";
106 oBackup.Initialize = true;
107 oBackup.SQLBackup(oSQLServer);
108
109 return true;
110 }
111 catch(Exception ex)
112 {
113 return false;
114 throw ex;
115 }
116 finally
117 {
118 oSQLServer.DisConnect();
119 }
120 }
121
122 /**//// <summary>
123 /// 数据库恢复
124 /// </summary>
125 public string DbRestore()
126 {
127 if(exepro()!=true)//执行存储过程
128 {
129 return "操作失败";
130 }
131 else
132 {
133 SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
134 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
135 try
136 {
137 exepro();
138 oSQLServer.LoginSecure = false;
139 oSQLServer.Connect(server, uid, pwd);
140 oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
141 oRestore.Database = database;
142 /**////自行修改
143 oRestore.Files = @"d:\aaa\aaa.bak";
144 oRestore.FileNumber = 1;
145 oRestore.ReplaceDatabase = true;
146 oRestore.SQLRestore(oSQLServer);
147
148 return "ok";
149 }
150 catch(Exception e)
151 {
152 return "恢复数据库失败";
153 throw e;
154 }
155 finally
156 {
157 oSQLServer.DisConnect();
158 }
159 }
160 }
161
162 /**//// <summary>
163 /// 杀死当前库的所有进程
164 /// </summary>
165 /// <returns></returns>
166 private bool exepro()
167 {
168
169 SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
170 SqlCommand cmd = new SqlCommand("killspid",conn1);
171 cmd.CommandType = CommandType.StoredProcedure;
172 cmd.Parameters.Add("@dbname","aaa");
173 try
174 {
175 conn1.Open();
176 cmd.ExecuteNonQuery();
177 return true;
178 }
179 catch(Exception ex)
180 {
181 return false;
182 }
183 finally
184 {
185 conn1.Close();
186 }
187 }
188
189 }
190
191}
192
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
在相应的按钮
1<asp:Button id="wbtn_Backup" runat="server" Width="60px" Text="备 份" CssClass="Button"></asp:Button>
单击事件里调用即可：
1/**//// <summary>
2 /// 备份按钮
3 /// </summary>
4 /// <param name="sender"></param>
5 /// <param name="e"></param>
6 private void wbtn_Backup_Click(object sender, System.EventArgs e)
7 {
8 DbOperate dbop = new DbOperate();
9 dbop.DbBackup();
10 }
2
3
4
5
6
7
8
9
10
转载于:https://www.cnblogs.com/hzuIT/articles/483715.html