用SQLDMO在ASP.NET中备份和还原数据库(SQLSERVER2000)

来源：互联网发布：everying软件搜图片编辑：程序博客网时间：2024/06/06 03:14

如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复我们知道，用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);

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);

27 return "ok";

28 }

29 catch(Exception e)

30 {

31 return "恢复数据库失败";

32 throw e;

33 }

34 finally

35 {

36 oSQLServer.DisConnect();

37 }

38 }

39 }

41 /// <summary>

42 /// 杀死当前库的所有进程

43 /// </summary>

44 /// <returns></returns>

45 private bool exepro()

46 {

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;

6namespace DbBackUp

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;

21 /// <summary>

22 /// 登录名

23 /// </summary>

24 private string uid;

26 /// <summary>

27 /// 登录密码

28 /// </summary>

29 private string pwd;

31 /// <summary>

32 /// 要操作的数据库

33 /// </summary>

34 private string database;

36 /// <summary>

37 /// 数据库连接字符串

38 /// </summary>

39 private string conn;

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 }

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 }

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 }

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 }