如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
来源:百度文库 编辑:神马文学网 时间:2024/03/29 18:35:58
如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复 收藏
首先需要添加对SQLDMO引用
1.实现数据库的备份:
1/**////
2 /// 数据库备份
3 ///
4 ///备份是否成功
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/**////
2 /// 数据库恢复
3 ///
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 /**////
42 /// 杀死当前库的所有进程
43 ///
44 ///
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 /**////
9 /// 创建人:suyiming
10 /// 创建时间:2007年8月1日
11 /// 功能描述:实现数据库的备份和还原
12 /// 更新记录:
13 ///
14 public class DbOperate
15 {
16 /**////
17 /// 服务器
18 ///
19 private string server;
20
21 /**////
22 /// 登录名
23 ///
24 private string uid;
25
26 /**////
27 /// 登录密码
28 ///
29 private string pwd;
30
31 /**////
32 /// 要操作的数据库
33 ///
34 private string database;
35
36 /**////
37 /// 数据库连接字符串
38 ///
39 private string conn;
40
41 /**////
42 /// DbOperate类的构造函数
43 /// 在这里进行字符串的切割,获取服务器,登录名,密码,数据库
44 ///
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 /**////
55 /// 切割字符串
56 ///
57 ///
58 ///
59 ///
60 ///
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 /**////
70 /// 构造文件名
71 ///
72 ///文件名
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 /**////
89 /// 数据库备份
90 ///
91 ///备份是否成功
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 /**////
123 /// 数据库恢复
124 ///
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 /**////
163 /// 杀死当前库的所有进程
164 ///
165 ///
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 单击事件里调用即可:
1/**////
2 /// 备份按钮
3 ///
4 ///
5 ///
6 private void wbtn_Backup_Click(object sender, System.EventArgs e)
7 {
8 DbOperate dbop = new DbOperate();
9 dbop.DbBackup();
10 } CREATE PROCEDURE killspid
(
@dbname varchar(20) --数据库的名称
)
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @spid int
SET @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
EXECUTE (@sql)
OPEN getspid
FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status<>-1
BEGIN
EXECUTE('kill '+@spid)
FETCH NEXT FROM getspid INTO @spid
END
CLOSE getspid
DEALLOCATE getspid
END
GO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/suyiming/archive/2007/11/26/1902273.aspx
首先需要添加对SQLDMO引用
1.实现数据库的备份:
1/**////
2 /// 数据库备份
3 ///
4 ///
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/**////
2 /// 数据库恢复
3 ///
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 /**////
42 /// 杀死当前库的所有进程
43 ///
44 ///
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 /**////
9 /// 创建人:suyiming
10 /// 创建时间:2007年8月1日
11 /// 功能描述:实现数据库的备份和还原
12 /// 更新记录:
13 ///
14 public class DbOperate
15 {
16 /**////
17 /// 服务器
18 ///
19 private string server;
20
21 /**////
22 /// 登录名
23 ///
24 private string uid;
25
26 /**////
27 /// 登录密码
28 ///
29 private string pwd;
30
31 /**////
32 /// 要操作的数据库
33 ///
34 private string database;
35
36 /**////
37 /// 数据库连接字符串
38 ///
39 private string conn;
40
41 /**////
42 /// DbOperate类的构造函数
43 /// 在这里进行字符串的切割,获取服务器,登录名,密码,数据库
44 ///
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 /**////
55 /// 切割字符串
56 ///
57 ///
58 ///
59 ///
60 ///
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 /**////
70 /// 构造文件名
71 ///
72 ///
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 /**////
89 /// 数据库备份
90 ///
91 ///
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 /**////
123 /// 数据库恢复
124 ///
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 /**////
163 /// 杀死当前库的所有进程
164 ///
165 ///
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
1/**////
2 /// 备份按钮
3 ///
4 ///
5 ///
6 private void wbtn_Backup_Click(object sender, System.EventArgs e)
7 {
8 DbOperate dbop = new DbOperate();
9 dbop.DbBackup();
10 } CREATE PROCEDURE killspid
(
@dbname varchar(20) --数据库的名称
)
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @spid int
SET @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
EXECUTE (@sql)
OPEN getspid
FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status<>-1
BEGIN
EXECUTE('kill '+@spid)
FETCH NEXT FROM getspid INTO @spid
END
CLOSE getspid
DEALLOCATE getspid
END
GO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/suyiming/archive/2007/11/26/1902273.aspx
如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
如何在ASP.NET环境下长期保存ASP.NET页面的session变量
Oracle 数据库的备份与恢复
Asp.NET 生成静态页面并分页的代码实现
数据库安全知识:关于 Oracle数据库的备份与恢复
如何用SQL备份和还原数据库
博客园 - 热衷于DotNet技术! - 在Asp.net页面中实现数据饼图
asp.net中怎样在页面间传值?
ASP.NET下MVC设计模式的实现
ASP.NET下MVC设计模式的实现
ASP.NET下MVC设计模式的实现-微软开发专栏-ASP.NET-天极网
数据库安全知识:关于 Oracle数据库的备份与恢复 - 瑞星反病毒资讯网
asp.net页面的生存周期
ASP.NET的网站新闻管理系统设计与实现
Oracle数据库逻辑备份的实现
用于运行 ASP.NET 的进程帐户必须具有对 IIS 元数据库(如 IIS://serv...
ASP.NET的页面——ASP.NET页面的生命周期
XP下NTFS数字证书的备份与恢复
在ASP.NET页面上添加AJAX控件后,不能调出窗口的解决办法:
在ASP.NET页面上添加AJAX控件后,不能调出窗口的解决办法:
当点击按钮如何在asp.net 2.0中重新加载当前的页面
ASP.NET系统用户权限设计与实现
Mysql的备份与恢复
驱动程序的备份与恢复