WPF C#開発におけるSQLDMOによるデータベースバックアップリストア操作における注意点

8522 ワード

まずSQLDMOファイルパッケージをダウンロードし、アドレス:SQLDMOをダウンロードします.
 
ダウンロード後、解凍を行い、次の手順に従います.
ステップ1:まずmsvcr 71.dll, SQLDMO.DLL, Resources/2052/sqldmo.rll,Resources/1033/sqldmo.rllはC:/Program Files/Microsoft SQL Server/80/tools/Binnディレクトリにコピーします.ステップ2:オープン開始、実行中にregsvr 32"C:/Program Files/Microsoft SQL Server/80/tools/Binn/sqldmo.dll"を入力sqldmoを登録する.dllファイル.
 
 
2回以上の操作を行った後も、アクセスには次のエラーが表示されます.
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に変更すればよい.
 
次に、データベース・バックアップのリストア操作の主なコードを添付します.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  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;
        }

        /// 
        ///           
        /// 
        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 = "";
        }

        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