数据库备份与还原

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         }

完整的操作类如下：

  1 using  System;
  2 using  System.Collections;
  3 using  System.Data;
  4 using  System.Data.SqlClient;
  5
  6 namespace  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         }