本文主要内容来源stswordman的介绍,开发环境为10,数据库为08R2
原文地址http://www.cnblogs.com/stswordman/archive/2006/08/06/469002.html。
/// ///备份方法 /// SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;");
SqlCommand cmdBK = new SqlCommand(); cmdBK.CommandType = CommandType.Text; cmdBK.Connection = conn; cmdBK.CommandText = @"backup database test to disk='C:\ba' with init";
try { conn.Open(); cmdBK.ExecuteNonQuery(); MessageBox.Show("Backup successed."); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); conn.Dispose(); }
/// ///还原方法 /// SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False"); conn.Open();
//KILL DataBase Process SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn); SqlDataReader dr; dr = cmd.ExecuteReader(); ArrayList list = new ArrayList(); while(dr.Read()) { list.Add(dr.GetInt16(0)); } dr.Close(); for(int i = 0; i < list.Count; i++) { cmd = new SqlCommand(string.Format("KILL {0}", list), conn); cmd.ExecuteNonQuery(); }
SqlCommand cmd1 = new SqlCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = " ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE ";//解决还原不成功问题,将数据库设置为离线 cmd1.Connection = conn; SqlCommand cmd2 = new SqlCommand(); cmd2.CommandType = CommandType.Text; cmd2.Connection = conn; cmd2.CommandText = "ALTER DATABASE [test] SET ONLINE WITH ROLLBACK IMMEDIATE";//解决还原不成功问题,将数据库设置为在线 SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text; cmdRT.Connection = conn; cmdRT.CommandText = @"restore database test from disk='C:\ba'";
try { cmdRT.ExecuteNonQuery(); MessageBox.Show("Restore successed.");
SqlConnection.ClearAllPools();//清理一下数据库连接池,否则会出现“在向服务器发送请求时发生传输级错误” } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); }
因为数据库单独安装在服务器上,所以对数据库进行恢复时选择的文件比较麻烦,根据网友提供思路:在数据库中建立一张单独的备份文件-文件路径表,用来记录并显示用户备份的数据文件;备份还原时,从数据库查询备份文件-文件路径表,供用户选择和恢复;如果文件不存在(误删),提示用户删除数据库记录。
未解决的问题:数据库表中记录可能与实际备份文件不一致,不能删除服务器上的数据库备份(无法做到像MS数据库管理那样进行远程浏览)。
|
请发表评论