C# 結合 SQL-DMO 實作 SQL Server 資料庫備份與還原

SQL-DMO 簡介

SQL-DMO (SQL Distributed Management Objects) 是 SQL Server 7.0/2000/2005 時代的管理物件模型，透過 COM 介面提供資料庫管理功能。雖然已被 SMO (SQL Server Management Objects) 取代，但在維護舊系統時仍常遇到。

專案需求與架構

• Windows Forms 介面，單一實例模式防止重複開啟
• 支援列舉可用 SQL Server 執行個體與資料庫
• 備份：指定路徑、自動命名（含時間戳）、進度條顯示
• 還原：關鍵功能——自動殺死目標資料庫的其他連線程序，避免「資料庫正在使用中」錯誤

核心程式碼解析

1. 列舉伺服器與資料庫

private void GetSQLServerList()
{
    SQLDMO._Application sqlApp = new SQLDMO.ApplicationClass();
    SQLDMO.NameList servers = sqlApp.ListAvailableSQLServers();
    for (int i = 0; i < servers.Count; i++)
    {
        object srv = servers.Item(i + 1); // DMO 集合索引從 1 開始
        if (srv != null) cboServers.Items.Add(srv);
    }
}

private void GetBackupDB()
{
    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
    srv.Connect(cboServers.Text, txtUserName.Text, txtPassWord.Text);
    foreach (SQLDMO.Database db in srv.Databases)
    {
        if (db.Name != null) cboDatabase.Items.Add(db.Name);
    }
}

2. 備份實作

private void BackupDB()
{
    string fileName = $"{txtSavePath.Text}\\{cboDatabase.Text}_{DateTime.Now:yyyyMMddHHmmss}.DAT";
    string deviceName = $"{cboDatabase.Text}bak";

    SQLDMO.BackupClass backup = new SQLDMO.BackupClass();
    SQLDMO.SQLServerClass sqlServer = new SQLDMO.SQLServerClass();
    
    backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
    backup.Initialize = true;
    backup.PercentComplete += (msg, pct) => progressBar1.Value = pct;

    try
    {
        sqlServer.LoginSecure = false;
        sqlServer.Connect(cboServers.Text, txtUserName.Text, txtPassWord.Text);
        backup.Database = cboDatabase.Text;
        backup.Files = fileName;
        backup.BackupSetName = deviceName;
        backup.BackupSetDescription = "資料備份";
        backup.SQLBackup(sqlServer);
    }
    finally
    {
        sqlServer.DisConnect();
    }
}

3. 還原與強制斷開連線（核心亮點）

private void RestoreDB()
{
    SQLDMO.RestoreClass restore = new SQLDMO.RestoreClass();
    SQLDMO.SQLServerClass sqlServer = new SQLDMO.SQLServerClass();
    restore.PercentComplete += (msg, pct) => progressBar1.Value = pct;

    try
    {
        sqlServer.Connect(cboServers.Text, txtUserName.Text, txtPassWord.Text);
        
        // 關鍵：枚舉並殺死目標資料庫的所有連線
        SQLDMO.QueryResults processes = sqlServer.EnumProcesses(-1);
        int spidCol = -1, dbNameCol = -1;
        
        for (int i = 1; i <= processes.Columns; i++)
        {
            string colName = processes.get_ColumnName(i).ToUpper().Trim();
            if (colName == "SPID") spidCol = i;
            else if (colName == "DBNAME") dbNameCol = i;
            if (spidCol != -1 && dbNameCol != -1) break;
        }

        for (int i = 1; i <= processes.Rows; i++)
        {
            int spid = processes.GetColumnLong(i, spidCol);
            string dbName = processes.GetColumnString(i, dbNameCol);
            if (dbName.Equals("CgRecord", StringComparison.OrdinalIgnoreCase))
            {
                sqlServer.KillProcess(spid); // 強制斷開
            }
        }

        restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
        restore.Database = cboDBtoBackup.Text;
        restore.Files = txtBackUpFile.Text;
        restore.FileNumber = 1;
        restore.ReplaceDatabase = true;
        restore.SQLRestore(sqlServer);
    }
    finally
    {
        sqlServer.DisConnect();
    }
}

現代替代方案建議

新專案請改用 SMO (Microsoft.SqlServer.Management.Smo) 或 T-SQL BACKUP/RESTORE 指令 搭配 SqlConnection 執行，優點：

• 原生 .NET 組件，無需 COM Interop
• 支援 SQL Server 2005+ 所有版本
• 效能更好、部署更簡單

// 使用 T-SQL 還原並強制斷開連線（現代做法）
string sql = @"
    ALTER DATABASE [目標資料庫] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    RESTORE DATABASE [目標資料庫] FROM DISK = @path WITH REPLACE;
    ALTER DATABASE [目標資料庫] SET MULTI_USER;";
using (var cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.AddWithValue("@path", backupPath);
    cmd.ExecuteNonQuery();
}