在c#中利用SQLDMO备份恢复数据库

里面注意：

1：在项目工程中引用sqldmo

2： 无论是备份路径还是恢复路径，都要使用"[]”把路径括起来，要不路径有空格就会报错

  1 private void buttonShuJuHuiFu_Click(object sender, EventArgs e)
  2        {
  3            DialogResult result = openFileDialogShuJuHuiFu.ShowDialog();
  4            if (result == DialogResult.OK)
  5            {
  6                formProgress = new FormProgress();
  7                string strBeiFenWenJian = openFileDialogShuJuHuiFu.FileName;
  8                formProgress.Show();
  9                System.Windows.Forms.Application.DoEvents();
 10                if (RestoreDB(server, user, password, "consumer", strBeiFenWenJian, formProgress.progressBar1))
 11                {
 12                    MessageBox.Show("数据库恢复成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 13                }
 14                formProgress.Close();
 15            }
 16            
 17            
 18        }
 19
 20        private void buttonShuJuBeiFen_Click(object sender, EventArgs e)
 21        {
 22            formProgress = new FormProgress();
 23            DialogResult result = folderBrowserDialogShuJuBeiFen.ShowDialog();            
 24            if (result == DialogResult.OK)
 25            {
 26                textBoxShuJuBeiFen.Text = folderBrowserDialogShuJuBeiFen.SelectedPath;
 27            }
 28            string strBeiFenLuJing = textBoxShuJuBeiFen.Text.Trim();
 29            if (strBeiFenLuJing.Length > 0)
 30            {
 31                if (!Directory.Exists(strBeiFenLuJing))
 32                {
 33                    strBeiFenLuJing = Application.StartupPath ; 
 34                }
 35            }
 36            else
 37            {
 38                strBeiFenLuJing = Application.StartupPath;
 39            }
 40            formProgress.Show();
 41            System.Windows.Forms.Application.DoEvents();
 42            if (BackUPDB(server, user, password, "consumer", strBeiFenLuJing + @"\" + "consumer" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bak", formProgress.progressBar1))
 43            {                
 44                MessageBox.Show("数据库备份成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 45            }
 46            formProgress.Close();
 47        }
 48
 49        private void button3_Click(object sender, EventArgs e)
 50        {
 51            this.Close();
 52        }
 53        /**//// <summary>
 54        /// 数据库的备份和实时进度显示
 55        /// </summary>
 56        /// <param name="ServerName">服务器名称</param>
 57        /// <param name="UserName">用户名</param>
 58        /// <param name="Password">用户密码</param>
 59        /// <param name="strDbName">数据库名称</param>
 60        /// <param name="strFileName">数据库文件</param>
 61        /// <param name="pgbMain">进度条</param>
 62        /// <returns></returns>
 63        public bool BackUPDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
 64        {            
 65            strFileName = "[" + strFileName + "]";
 66            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
 67            try
 68            {
 69                svr.LoginSecure = false;
 70                svr.Connect(ServerName, UserName, Password);
 71                SQLDMO.Backup bak = new SQLDMO.BackupClass();
 72                bak.Action = 0 ;
 73                bak.Initialize = true;
 74                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(formProgress.Step);
 75                bak.PercentComplete += pceh;
 76                bak.Files = strFileName;
 77                bak.Database = strDbName;
 78                bak.SQLBackup(svr);
 79
 80                return true;
 81            }
 82            catch (Exception err)
 83            {
 84                throw (new Exception("备份数据库失败" + err.Message));
 85            }
 86            finally
 87            {
 88                svr.DisConnect();
 89            }
 90        }
 91
 92        /**//// <summary>
 93        /// 数据库的恢复
 94        /// </summary>
 95        /// <param name="ServerName">服务器名称</param>
 96        /// <param name="UserName">用户名</param>
 97        /// <param name="Password">用户密码</param>
 98        /// <param name="strDbName">数据库名称</param>
 99        /// <param name="strFileName">数据库文件</param>
        /// <param name="pgbMain">进度条</param>
        /// <returns></returns>
        public bool RestoreDB(string ServerName, string UserName, string Password, string strDbName, string strFileName, ProgressBar pgbMain)
        {
            strFileName = "[" + strFileName + "]";
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.LoginSecure = false;
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                int iColPIDNum = -1;
                int iColDbName = -1;
                for (int i = 1; i <= qr.Columns; i++)
                {
                    string strName = qr.get_ColumnName(i);
                    if (strName.ToUpper().Trim() == "SPID")
                    {
                        iColPIDNum = i;
                    }
                    else if (strName.ToUpper().Trim() == "DBNAME")
                    {
                        iColDbName = i;
                    }
                    if (iColPIDNum != -1 && iColDbName != -1)
                        break;
                }

                for (int i = 1; i <= qr.Rows; i++)
                {
                    int lPID = qr.GetColumnLong(i, iColPIDNum);
                    string strDBName = qr.GetColumnString(i, iColDbName);
                    if (strDBName.ToUpper() == strDbName.ToUpper())
                        svr.KillProcess(lPID);
                }


                SQLDMO.Restore res = new SQLDMO.RestoreClass();
                res.Action = 0;
                SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(formProgress.Step);
                res.PercentComplete += pceh;
                res.Files = strFileName;

                res.Database = strDbName;
                res.ReplaceDatabase = true;
                res.SQLRestore(svr);
                return true;
            }
            catch (Exception err)
            {
                throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message));
            }
            finally
            {
                svr.DisConnect();
            }
        }