1 有两种方法，都是保存为.bak文件。一种是直接用Sql语句执行，另一种是通过引用SQL Server的SQLDMO组件来实现:
2 1.通过执行Sql语句来实现
3 注意，用Sql语句实现备份与还原操作时，最好不要使用需要备份或还原的数据库连接，而使用master，否则可能会出现如下三个问题：(1)超时时间已到。在操作完成之前超时时间已过或服务器未响应。(2) 在向服务器发送请求时发生传输级错误。(provider:共享内存提供程序,error:0-系统无法打开文件。) (3)从服务器接收结果时发生传输级错误。(provider:共享内存提供程序，error:0 - 系统无法打开文件。) ，如果一定要用这个连接的话，要注意在执行Sql语句前加个Sql语句：use master，这样可能会解决以上问题。
4 (1)数据备份语句：backup database 数据库名 to disk='保存路径\dbName.bak'
5 (2)数据恢复语句：restore database 数据库名 from disk='保存路径\dbName.bak' WITH MOVE 'dbName_Data' TO 'c:\tcomcrm20041217.mdf', --数据文件还原后存放的新位置
6 MOVE 'dbName_Log' TO 'c:\comcrm20041217.ldf' ----日志文件还原后存放的新位置
7 关于这两个语句还有更详细的介绍：http://blog.csdn.net/holyrong/archive/2007/08/29/1764105.aspx
8 //数据库备份与恢复实例
9 private void btnBak_Click(object sender, EventArgs e) //备份
10 {
11 string saveAway = this.tbxBakLoad.Text.ToString().Trim();
12 string cmdText = @"backup database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " to disk='" + saveAway + "'";
13 BakReductSql(cmdText,true);
14 }
15 private void btnReduct_Click(object sender, EventArgs e) //恢复
16 {
17 string openAway = this.tbxReductLoad.Text.ToString().Trim();//读取文件的路径
18 string cmdText = @"restore database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " from disk='" + openAway + "'";
19 BakReductSql(cmdText,false);
20 }
21 /// <summary>
22 /// 对数据库的备份和恢复操作，Sql语句实现
23 /// </summary>
24 /// <param name="cmdText">实现备份或恢复的Sql语句 </param>
25 /// <param name="isBak">该操作是否为备份操作，是为true否，为false </param>
26 private void BakReductSql(string cmdText,bool isBak)
27 {
28 SqlCommand cmdBakRst = new SqlCommand();
29 SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;uid=sa;pwd=;");
30 try
31 {
32 conn.Open();
33 cmdBakRst.Connection = conn;
34 cmdBakRst.CommandType = CommandType.Text;
35 if (!isBak) //如果是恢复操作
36 {
37 string setOffline = "Alter database GroupMessage Set Offline With rollback immediate ";
38 string setOnline = " Alter database GroupMessage Set Online With Rollback immediate";
39 cmdBakRst.CommandText = setOffline + cmdText + setOnline;
40 }
41 else
42 {
43 cmdBakRst.CommandText = cmdText;
44 }
45 cmdBakRst.ExecuteNonQuery();
46 if (!isBak)
47 {
48 MessageBox.Show("恭喜你，数据成功恢复为所选文档的状态！", "系统消息");
49 }
50 else
51 {
52 MessageBox.Show("恭喜，你已经成功备份当前数据！", "系统消息");
53 }
54 }
55 catch (SqlException sexc)
56 {
57 MessageBox.Show("失败，可能是对数据库操作失败，原因：" + sexc, "数据库错误消息");
58 }
59 catch (Exception ex)
60 {
61 MessageBox.Show("对不起，操作失败，可能原因：" + ex, "系统消息");
62 }
63 finally
64 {
65 cmdBakRst.Dispose();
66 conn.Close();
67 conn.Dispose();
68 }
69 }
70 另外，如果出现：“尚未备份数据库的日志尾部”错误，可以在还原语句后加上 With Replace 或 With stopat
71
72 2.用SQLDMO实现（下面代码引用别人的）
73 //数据库备份
74 string backaway =textbox1.Text.Trim();
75 SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
76 SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
77 try
78 {
79 oSQLServer.LoginSecure = false;
80 //下面设置登录sql服务器的ip,登录名,登录密码
81 oSQLServer.Connect(serverip, serverid, serverpwd);
82 oBackup.Action = 0;
83 //下面两句是显示进度条的状态
84 SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);
85 oBackup.PercentComplete += pceh;
86 //数据库名称:
87 oBackup.Database = "k2";
88 //备份的路径
89 oBackup.Files = @backaway;
90 //备份的文件名
91 oBackup.BackupSetName = "k2";
92 oBackup.BackupSetDescription = "数据库备份";
93 oBackup.Initialize = true;
94 oBackup.SQLBackup(oSQLServer);
95 MessageBox.Show("备份成功！", "提示");
96 }
97 catch
98 {
99 MessageBox.Show("备份失败！", "提示");
100 }
101 finally
102 {
103 oSQLServer.DisConnect();
104 }
105
106
107 //数据库恢复
108 //获取恢复的路径
109 string dbaway = textbox2.Text.Trim();
110 SQLDMO.Restore restore = new SQLDMO.RestoreClass();
111 SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
112 server.Connect(serverip, serverid, serverpwd);
113
114 //KILL DataBase Process
115 conn = new 工资管理系统.CCUtility.connstring();
116 conn.DBOpen();
117 SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='k2'", conn.Connection);
118 SqlDataReader dr = cmd.ExecuteReader();
119 while (dr.Read())
120 {
121 server.KillProcess(Convert.ToInt32(dr[0].ToString()));
122 }
123 dr.Close();
124 conn.DBClose();
125
126 try
127 {
128 restore.Action = 0;
129 SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
130 restore.PercentComplete += pceh;
131 restore.Database = "k2";
132 restore.Files = @dbaway;
133 restore.ReplaceDatabase = true;
134 restore.SQLRestore(server);
135 MessageBox.Show("数据库恢复成功！");
136 }
137 catch (Exception ex)
138 {
139 MessageBox.Show(ex.Message);
140 }
141 finally
142 {
143 server.DisConnect();
144 }
145
146 恢复相关的参数和备份相同,不再解释,自己看一下.
147
148 上面两个函数调用到了更改进度条的两个函数:
149
150 private void Step2(string message, int percent)
151 {
152 progressBar2.Value = percent;
153 }
154
155 private void Step(string message, int percent)
156 {
157 progressBar1.Value = percent;
158 }
159
160 setp对应备份,,setp2对应恢复....
浙公网安备 33010602011771号