我们知道，用sqldmo可以实现对数据库的备份与恢复，下面给出简单的实现方法。
首先需要添加对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.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程
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 }
完整的操作类如下：
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
在相应的按钮
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 }
新闻热点
疑难解答
图片精选
