在C#中運用SQLDMO備份和恢複SQL Server資料庫)

在C#中運用SQLDMO備份和恢複SQL Server資料庫 SQLDMO(SQL Distributed Management Objects，SQL分散式管理物件)封裝了Microsoft SQL Server資料庫中的對象。SQLDMO是Microsoft SQL Server中企業管理器所使用的應用程式介面，所以它可以執行很多功能，其中當然也包括對資料庫的備份和恢複。

SQLDMO由Microsoft SQL Server內建的SQLDMO.dll提供，由於SQLDMO.dll是一個COM對象（有關COM的概念，請參看我的COM與COM+技術那篇文章），所以大家在用之前必須在.NET項目中添加對它的引用。

下面是用C#語言書寫的用於Microsoft SQL ServerDatabase Backup和恢複的類：

public  sealed  class  DbOper{     ///<summary>     /// DbOper類的建構函式///作者：Shadow     ///</summary>     private DbOper()     {     }      ///<summary>     /// Database Backup     ///</summary>     public  static  void DbBackup()     {          SQLDMO.Backup oBackup = new SQLDMO.BackupClass();          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();         try         {              oSQLServer.LoginSecure = false;              oSQLServer.Connect("localhost", "sa", "1234");              oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;              oBackup.Database = "Northwind";              oBackup.Files = @"d:\Northwind.bak";              oBackup.BackupSetName = "Northwind";              oBackup.BackupSetDescription = "Database Backup";              oBackup.Initialize = true;              oBackup.SQLBackup(oSQLServer);         }         catch         {              throw;         }          finally         {              oSQLServer.DisConnect();         }     }其中這個語句取得了所有的進程列表：
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) ;
}
      ///<summary>     /// 資料庫恢複     ///</summary>     public  static  void DbRestore()     {          SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();          SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();         try         {              oSQLServer.LoginSecure = false;              oSQLServer.Connect("localhost", "sa", "1234");              oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;              oRestore.Database = "Northwind";              oRestore.Files = @"d:\Northwind.bak";              oRestore.FileNumber = 1;              oRestore.ReplaceDatabase = true;              oRestore.SQLRestore(oSQLServer);         }         catch         {              throw;         }          finally         {              oSQLServer.DisConnect();         }     }}

註冊SQLDMO.dll用到的組件

SQLDMO.DLL
SQLDMO.rll
sqlresld.dl
sqlsvc.dll
sqlsvc.rll
Sqlwid.dll
Sqlwoa.dll
w95scm.dll

註：如果註冊不成功請更新MDAC版本至2.8