C#实现SQL数据库备份与恢复

来源：互联网发布：python datetime模块编辑：程序博客网时间：2024/06/05 07:31

C#实现SQL数据库备份与恢复 有两种方法，都是保存为.bak文件。一种是直接用Sql语句执行，另一种是通过引用SQL Server的SQLDMO组件来实现: 1.通过执行Sql语句来实现注意，用Sql语句实现备份与还原操作时，最好不要使用需要备份或还原的数据库连接，而使用master，否则可能会出现如下三个问题：(1)超时时间已到。在操作完成之前超时时间已过或服务器未响应。(2)  在向服务器发送请求时发生传输级错误。(provider:共享内存提供程序,error:0-系统无法打开文件。)  (3)从服务器接收结果时发生传输级错误。(provider:共享内存提供程序，error:0   -   系统无法打开文件。) ，如果一定要用这个连接的话，要注意在执行Sql语句前加个Sql语句：use master，这样可能会解决以上问题。     (1)数据备份语句：backup database  数据库名 to disk='保存路径\dbName.bak'     (2)数据恢复语句：restore database 数据库名 from disk='保存路径\dbName.bak'   WITH MOVE 'dbName_Data' TO 'c:\tcomcrm20041217.mdf', --数据文件还原后存放的新位置MOVE 'dbName_Log' TO 'c:\comcrm20041217.ldf' ----日志文件还原后存放的新位置关于这两个语句还有更详细的介绍：http://blog.csdn.net/holyrong/archive/2007/08/29/1764105.aspx             //数据库备份与恢复实例            private void btnBak_Click(object sender, EventArgs e) //备份        {             string saveAway = this.tbxBakLoad.Text.ToString().Trim();             string cmdText = @"backup database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " to disk='" + saveAway + "'";             BakReductSql(cmdText,true);                     }        private void btnReduct_Click(object sender, EventArgs e)  //恢复        {             string openAway = this.tbxReductLoad.Text.ToString().Trim();//读取文件的路径            string cmdText = @"restore database " + System.Configuration.ConfigurationSettings.AppSettings["dbName"] + " from disk='" + openAway + "'";                         BakReductSql(cmdText,false);         }        /// <summary>         /// 对数据库的备份和恢复操作，Sql语句实现        /// </summary>         /// <param name="cmdText">实现备份或恢复的Sql语句</param>         /// <param name="isBak">该操作是否为备份操作，是为true否，为false</param>         private void BakReductSql(string cmdText,bool isBak)         {             SqlCommand cmdBakRst = new SqlCommand();             SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;uid=sa;pwd=;");            try             {                 conn.Open();                 cmdBakRst.Connection = conn;                 cmdBakRst.CommandType = CommandType.Text;                 if (!isBak)     //如果是恢复操作                {                     string setOffline = "Alter database GroupMessage Set Offline With rollback immediate ";                     string setOnline = " Alter database GroupMessage Set Online With Rollback immediate";                     cmdBakRst.CommandText = setOffline + cmdText + setOnline;                }                 else                 {                     cmdBakRst.CommandText = cmdText;                 }                 cmdBakRst.ExecuteNonQuery();                 if (!isBak)                 {                     MessageBox.Show("恭喜你，数据成功恢复为所选文档的状态！", "系统消息");                 }                 else                 {                     MessageBox.Show("恭喜，你已经成功备份当前数据！", "系统消息");                 }             }             catch (SqlException sexc)             {                 MessageBox.Show("失败，可能是对数据库操作失败，原因：" + sexc, "数据库错误消息");             }             catch (Exception ex)             {                 MessageBox.Show("对不起，操作失败，可能原因：" + ex, "系统消息");             }             finally             {                 cmdBakRst.Dispose();                 conn.Close();                 conn.Dispose();             }         }另外，如果出现：“尚未备份数据库的日志尾部”错误，可以在还原语句后加上 With Replace 或 With stopat                    2.用SQLDMO实现（下面代码引用别人的）    //数据库备份        string backaway =textbox1.Text.Trim();             SQLDMO.Backup oBackup = new SQLDMO.BackupClass();             SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();             try             {                 oSQLServer.LoginSecure = false;                 //下面设置登录sql服务器的ip,登录名,登录密码                oSQLServer.Connect(serverip, serverid, serverpwd);                 oBackup.Action = 0;                //下面两句是显示进度条的状态                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step2);                 oBackup.PercentComplete += pceh;                 //数据库名称:                 oBackup.Database = "k2";                 //备份的路径                oBackup.Files = @backaway;                 //备份的文件名                oBackup.BackupSetName = "k2";                 oBackup.BackupSetDescription = "数据库备份";                 oBackup.Initialize = true;                 oBackup.SQLBackup(oSQLServer);                 MessageBox.Show("备份成功！", "提示");             }             catch             {                 MessageBox.Show("备份失败！", "提示");             }             finally             {                 oSQLServer.DisConnect();             } //数据库恢复        //获取恢复的路径        string dbaway = textbox2.Text.Trim();             SQLDMO.Restore restore = new SQLDMO.RestoreClass();             SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();             server.Connect(serverip, serverid, serverpwd);             //KILL DataBase Process             conn = new 工资管理系统.CCUtility.connstring();             conn.DBOpen();             SqlCommand cmd = new SqlCommand("use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='k2'", conn.Connection);             SqlDataReader dr = cmd.ExecuteReader();             while (dr.Read())             {                 server.KillProcess(Convert.ToInt32(dr[0].ToString()));             }             dr.Close();             conn.DBClose();             try             {                 restore.Action = 0;                 SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);                  restore.PercentComplete += pceh;                 restore.Database = "k2";                 restore.Files = @dbaway;                 restore.ReplaceDatabase = true;                 restore.SQLRestore(server);                 MessageBox.Show("数据库恢复成功！");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 server.DisConnect();             }恢复相关的参数和备份相同,不再解释,自己看一下.上面两个函数调用到了更改进度条的两个函数:        private void Step2(string message, int percent)         {             progressBar2.Value = percent;         }         private void Step(string message, int percent)         {             progressBar1.Value = percent;         } setp对应备份,,setp2对应恢复.... 
转自 http://www.cnblogs.com/enjoyprogram/p/3181682.html
0 0