在c#中运用sqldmo备份和恢复sqlserver数据库(重点)

我们知道，用SQLDMO可以实现对数据库的备份与恢复，下面给出简单的实现方法。
首先需要添加对SQLDMO引用

1.实现数据库的备份：

1/**////<summary>
2///数据库备份
3///</summary>
4///<returns>备份是否成功</returns>
5publicboolDbBackup()
6{
7stringpath=CreatePath();
8SQLDMO.BackupoBackup=newSQLDMO.BackupClass();
9SQLDMO.SQLServeroSQLServer=newSQLDMO.SQLServerClass();
10try
11{
12oSQLServer.LoginSecure=false;
13oSQLServer.Connect(server,uid,pwd);
14oBackup.Action=SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15oBackup.Database=database;
16oBackup.Files=path;
17oBackup.BackupSetName=database;
18oBackup.BackupSetDescription="数据库备份";
19oBackup.Initialize=true;
20oBackup.SQLBackup(oSQLServer);
21
22returntrue;
23}
24catch(Exceptionex)
25{
26returnfalse;
27throwex;
28}
29finally
30{
31oSQLServer.DisConnect();
32}
33}

2.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程

1/**////<summary>
2///数据库恢复
3///</summary>
4publicstringDbRestore()
5{
6if(exepro()!=true)//执行存储过程
7{
8return"操作失败";
9}
10else
11{
12SQLDMO.RestoreoRestore=newSQLDMO.RestoreClass();
13SQLDMO.SQLServeroSQLServer=newSQLDMO.SQLServerClass();
14try
15{
16exepro();
17oSQLServer.LoginSecure=false;
18oSQLServer.Connect(server,uid,pwd);
19oRestore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
20oRestore.Database=database;
21/**////自行修改
22oRestore.Files=@"d:/aaa/aaa.bak";
23oRestore.FileNumber=1;
24oRestore.ReplaceDatabase=true;
25oRestore.SQLRestore(oSQLServer);
26
27return"ok";
28}
29catch(Exceptione)
30{
31return"恢复数据库失败";
32throwe;
33}
34finally
35{
36oSQLServer.DisConnect();
37}
38}
39}
40
41/**////<summary>
42///杀死当前库的所有进程
43///</summary>
44///<returns></returns>
45privateboolexepro()
46{
47
48SqlConnectionconn1=newSqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
49SqlCommandcmd=newSqlCommand("killspid",conn1);
50cmd.CommandType=CommandType.StoredProcedure;
51cmd.Parameters.Add("@dbname","aaa");
52try
53{
54conn1.Open();
55cmd.ExecuteNonQuery();
56returntrue;
57}
58catch(Exceptionex)
59{
60returnfalse;
61}
62finally
63{
64conn1.Close();
65}
66}

完整的操作类如下：

1usingSystem;
2usingSystem.Collections;
3usingSystem.Data;
4usingSystem.Data.SqlClient;
5
6namespaceDbBackUp
7{
8/**////<summary>
9///创建人：Terrylee
10///创建时间：2005年8月1日
11///功能描述：实现数据库的备份和还原
12///更新记录：
13///</summary>
14publicclassDbOperate
15{
16/**////<summary>
17///服务器
18///</summary>
19privatestringserver;
20
21/**////<summary>
22///登录名
23///</summary>
24privatestringuid;
25
26/**////<summary>
27///登录密码
28///</summary>
29privatestringpwd;
30
31/**////<summary>
32///要操作的数据库
33///</summary>
34privatestringdatabase;
35
36/**////<summary>
37///数据库连接字符串
38///</summary>
39privatestringconn;
40
41/**////<summary>
42///DbOperate类的构造函数
43///在这里进行字符串的切割，获取服务器，登录名，密码，数据库
44///</summary>
45publicDbOperate()
46{
47conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
48server=StringCut(conn,"server=",";");
49uid=StringCut(conn,"uid=",";");
50pwd=StringCut(conn,"pwd=",";");
51database=StringCut(conn,"database=",";");
52}
53
54/**////<summary>
55///切割字符串
56///</summary>
57///<paramname="str"></param>
58///<paramname="bg"></param>
59///<paramname="ed"></param>
60///<returns></returns>
61publicstringStringCut(stringstr,stringbg,stringed)
62{
63stringsub;
64sub=str.Substring(str.IndexOf(bg)+bg.Length);
65sub=sub.Substring(0,sub.IndexOf(";"));
66returnsub;
67}
68
69/**////<summary>
70///构造文件名
71///</summary>
72///<returns>文件名</returns>
73privatestringCreatePath()
74{
75stringCurrTime=System.DateTime.Now.ToString();
76CurrTime=CurrTime.Replace("-","");
77CurrTime=CurrTime.Replace(":","");
78CurrTime=CurrTime.Replace("","");
79CurrTime=CurrTime.Substring(0,12);
80stringpath=@"d://aaa//";
81path+=database;
82path+="_db_";
83path+=CurrTime;
84path+=".BAK";
85returnpath;
86}
87
88/**////<summary>
89///数据库备份
90///</summary>
91///<returns>备份是否成功</returns>
92publicboolDbBackup()
93{
94stringpath=CreatePath();
95SQLDMO.BackupoBackup=newSQLDMO.BackupClass();
96SQLDMO.SQLServeroSQLServer=newSQLDMO.SQLServerClass();
97try
98{
99oSQLServer.LoginSecure=false;
100oSQLServer.Connect(server,uid,pwd);
101oBackup.Action=SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
102oBackup.Database=database;
103oBackup.Files=path;
104oBackup.BackupSetName=database;
105oBackup.BackupSetDescription="数据库备份";
106oBackup.Initialize=true;
107oBackup.SQLBackup(oSQLServer);
108
109returntrue;
110}
111catch(Exceptionex)
112{
113returnfalse;
114throwex;
115}
116finally
117{
118oSQLServer.DisConnect();
119}
120}
121
122/**////<summary>
123///数据库恢复
124///</summary>
125publicstringDbRestore()
126{
127if(exepro()!=true)//执行存储过程
128{
129return"操作失败";
130}
131else
132{
133SQLDMO.RestoreoRestore=newSQLDMO.RestoreClass();
134SQLDMO.SQLServeroSQLServer=newSQLDMO.SQLServerClass();
135try
136{
137exepro();
138oSQLServer.LoginSecure=false;
139oSQLServer.Connect(server,uid,pwd);
140oRestore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
141oRestore.Database=database;
142/**////自行修改
143oRestore.Files=@"d:/aaa/aaa.bak";
144oRestore.FileNumber=1;
145oRestore.ReplaceDatabase=true;
146oRestore.SQLRestore(oSQLServer);
147
148return"ok";
149}
150catch(Exceptione)
151{
152return"恢复数据库失败";
153throwe;
154}
155finally
156{
157oSQLServer.DisConnect();
158}
159}
160}
161
162/**////<summary>
163///杀死当前库的所有进程
164///</summary>
165///<returns></returns>
166privateboolexepro()
167{
168
169SqlConnectionconn1=newSqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
170SqlCommandcmd=newSqlCommand("killspid",conn1);
171cmd.CommandType=CommandType.StoredProcedure;
172cmd.Parameters.Add("@dbname","aaa");
173try
174{
175conn1.Open();
176cmd.ExecuteNonQuery();
177returntrue;
178}
179catch(Exceptionex)
180{
181returnfalse;
182}
183finally
184{
185conn1.Close();
186}
187}
188
189}
190
191}
192

在相应的按钮

1<asp:Buttonid="wbtn_Backup"runat="server"Width="60px"Text="备份"CssClass="Button"></asp:Button>

单击事件里调用即可：

1/**////<summary>
2///备份按钮
3///</summary>
4///<paramname="sender"></param>
5///<paramname="e"></param>
6privatevoidwbtn_Backup_Click(objectsender,System.EventArgse)
7{
8DbOperatedbop=newDbOperate();
9dbop.DbBackup();
10}

^_^