WPF C#開發中利用SQLDMO進行Database Backup還原作業中的注意點

首先下載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檔案。

 

 

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

Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80070005.

說明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即可。

 

下面附上Database Backup還原作業的主要代碼：

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

  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 SQLDatabase Backup函數        /// < summary>        /// SQLDatabase Backup        /// < /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 = "Database Backup";                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">Database Backup的路徑< /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