首页 > 数据库技术 > 详细

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

时间:2014-02-18 08:05:49      阅读:720      评论:0      收藏:0      [点我收藏+]

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对应恢复....

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

原文:http://www.cnblogs.com/yxyzy/p/3551707.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!