WPF C#開發中利用SQLDMO進行資料庫備份還原操作中的注意點

首先下載SQLDMO檔案包，下載地址：SQLDMO

下載後，進行解壓，然後按照下面的步驟進行操作：

第一步：首先將msvcr71.dll, SQLDMO.DLL, Resources/2052/sqldmo.rll,Resources/1033/sqldmo.rll 拷貝到C:/Program Files/Microsoft SQL Server/80/Tools/Binn目錄。

第二步：開啟開始，在執行中輸入 regsvr32 "C:/Program Files/Microsoft SQL Server/80/Tools/Binn/sqldmo.dll" 註冊sqldmo.dll檔案。

如果經過以上兩次操作後，訪問依然提示如下錯誤：

說明C:/Program Files/資料夾僅有Administrator和System的控制許可權，而沒有其他任何使用者的許可權，因此我們為Microsoft SQL Server資料夾增加上Network Service 的讀取許可權。

即使進行註冊後，執行程式中還會報錯，類似如下資訊：

“event invocation for COM objects requires event to be attributed with DispIdAttribute”

這個原因是在專案Bin中引用SQLDMO.dll檔案後，該dll屬性中的“嵌入互操作型別”的值預設是True造成的，修改成False即可。

下面附上資料庫備份還原操作的主要程式碼：

----------------------------------資料備份------------------------------------------------

  private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            //備份
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "(*.mdf)|*.mdf";
            saveFileDialog.FilterIndex = 0;
            if (saveFileDialog.ShowDialog() == true)
            {
                string filePath = saveFileDialog.FileName;
                SQLBACK(Global.DB_SOURCE, Global.DB_UID, Global.DB_UPWD, Global.DB_NAME, filePath);
            }
        }

        #region SQL資料庫備份函式

        /// < summary>
        /// SQL資料庫備份
        /// < /summary>
        /// < param name="ServerIP">SQL伺服器IP或(Localhost)< /param>
        /// < param name="LoginUserName">資料庫登入名< /param>
        /// < param name="LoginPass">資料庫登入密碼< /param>
        /// < param name="DBName">資料庫名< /param>
        /// < param name="BackPath">備份到的路徑< /param>
        public  void SQLBACK(string ServerIP, string LoginUserName, string LoginPass, string DBName, string BackPath)
        {

            SQLDMO.Backup oBackup = new SQLDMO.Backup();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServer();
            oBackup.Action = 0;
            oBackup.Initialize = true;
            SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
            oBackup.PercentComplete += pceh;
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = DBName;
                oBackup.Files = @"" + BackPath + "";
                oBackup.BackupSetName = DBName;
                oBackup.BackupSetDescription = "資料庫備份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                MessageBox.Show("備份成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("備份失敗，原因為：" + ex.Message);
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

        private void Step(string message, int percent)
       {
              this.pb.Value = percent;
       }

      --------------------------------資料還原----------------------------------------------------------

 private static DataRestore _Instance = null; //頁面例項

        public DataRestore()
        {
            InitializeComponent();
        }

        public static DataRestore Instance()
        {
            if (_Instance == null)
            {
                _Instance = new DataRestore();
            }
            else
            {
                MessageBox.Show("已經有一個例項在執行！");
            }
            return _Instance;
        }

        /// <summary>
        /// 獲取資料庫伺服器列表
        /// </summary>
        private void GetSQLServerList()
        {
            SQLDMO._Application sqlApp = new SQLDMO.Application();
            SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
            for (int i = 0; i < sqlServers.Count; i++)
            {
                object srv = sqlServers.Item(i + 1);
                if (srv != null)
                {
                    // this.cboServers.Items.Add(srv);
                }
            }
            //if (this.cboServers.Items.Count > 0)
            //    this.cboServers.SelectedIndex = 0;
            //else
            //    this.cboServers.Text = "<No available SQL Servers>";
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            //還原
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "(*.mdf)|*.mdf";
            openFileDialog.FilterIndex = 0;
            if (openFileDialog.ShowDialog() == true)
            {
                string filePath = openFileDialog.FileName; //獲取選中的備份檔案，進行還原操作
                SQLDbRestore(Global.DB_SOURCE, Global.DB_UID, Global.DB_UPWD, Global.DB_NAME, filePath);
            }
        }

        #region SQL恢復資料庫

        /// < summary>
        /// SQL恢復資料庫
        /// < /summary>
        /// < param name="ServerIP">SQL伺服器IP或(Localhost)< /param>
        /// < param name="LoginUserName">資料庫登入名< /param>
        /// < param name="LoginPass">資料庫登入密碼< /param>
        /// < param name="DBName">要還原的資料庫名< /param>
        /// < param name="BackPath">資料庫備份的路徑< /param>
        public void SQLDbRestore(string ServerIP, string LoginUserName, string LoginPass, string DBName, string BackPath)
        {
            SQLDMO.Restore oRestore = new SQLDMO.Restore();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServer();
            oRestore.Action = 0;
            //SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
            //oRestore.PercentComplete += pceh;
            try
            {
                oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
                SQLDMO.QueryResults qr = oSQLServer.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() == "JCWZDB".ToUpper())
                    {
                        oSQLServer.KillProcess(lPID);
                    }
                }
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = DBName;
                oRestore.Files = BackPath;
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
                MessageBox.Show("資料還原成功！");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("資料還原失敗： " + ex.ToString());
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

        private void Step(string message, int percent)
        {
            this.pb.Value = percent;
        }

        #endregion