如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
需要注意的几点:
1: killspid存储过程必需要放到数据库master的存储过程中.
2:killspid存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[p_killspid]
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
我们知道，用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 }
/**//// <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
try11
{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
finally30
{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 }
/**//// <summary>2
/// 数据库恢复3
/// </summary>4
public string DbRestore()5
{6
if(exepro()!=true)//执行存储过程7
{8
return "操作失败";9
}10
else11
{12
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();13
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();14
try15
{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
finally35
{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
try53
{54
conn1.Open();55
cmd.ExecuteNonQuery();56
return true;57
}58
catch(Exception ex)59
{60
return false;61
}62
finally63
{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
using System;2
using System.Collections;3
using System.Data;4
using System.Data.SqlClient;5
6
namespace DbBackUp7
{8
/**//// <summary>9
/// 创建人：Terrylee10
/// 创建时间：2005年8月1日11
/// 功能描述：实现数据库的备份和还原12
/// 更新记录：13
/// </summary>14
public class DbOperate15
{ 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
try98
{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
finally117
{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
else132
{133
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();134
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();135
try136
{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
finally156
{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
try174
{175
conn1.Open();176
cmd.ExecuteNonQuery();177
return true;178
}179
catch(Exception ex)180
{181
return false;182
}183
finally184
{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>
单击事件里调用即可：<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 }
转载自:http://terrylee.cnblogs.com/archive/2005/10/13/253667.html /**//// <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
}
