使用 SQL-DMO 實作 SQL Server 動態備份與還原 (C# 完整範例)

SQL-DMO 簡介與適用場景

SQL-DMO (SQL Distributed Management Objects) 是 SQL Server 2000 時代的 COM 管理介面，封裝企業管理器所有功能。雖然已被 SMO (SQL Server Management Objects) 取代，但在維護舊系統、SQL Server 2000/2005 相容環境時仍具實用價值。

SQL-DMO 註冊與部署步驟

1. 註冊 DLL：

   regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"

2. 複製支援檔案 (避免「找不到資源」錯誤)：

   copy "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.rll" 
        "C:\Windows\System32\Resources\2052\SQLDMO.rll"
   mkdir "C:\Windows\System32\Resources\2052" 2>nul

3. 專案參考：Visual Studio → 加入參考 → COM → Microsoft SQLDMO Object Library
4. 命名空間：using SQLDMO;

C# 完整備份實作

using System;
using SQLDMO;

public class SqlBackupManager
{
    private string _server, _user, _pwd;

    public SqlBackupManager(string server, string user, string pwd)
    {
        _server = server; _user = user; _pwd = pwd;
    }

    /// 

    /// 完整資料庫備份
    /// 
    /// 來源資料庫名稱
    /// 備份檔完整路徑 (.bak)
    /// 備份集名稱
    /// 備份描述
    public void BackupDatabase(string databaseName, string backupPath, 
                               string backupSetName, string description)
    {
        SQLServerClass sqlServer = null;
        BackupClass backup = null;

        try
        {
            sqlServer = new SQLServerClass();
            sqlServer.LoginSecure = false;  // SQL Server 驗證
            sqlServer.Connect(_server, _user, _pwd);

            backup = new BackupClass();
            backup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
            backup.Database = databaseName;
            backup.Files = backupPath;
            backup.BackupSetName = backupSetName;
            backup.BackupSetDescription = description;
            backup.Initialize = true;  // 覆寫現有備份檔
            backup.SQLBackup(sqlServer);
        }
        catch (Exception ex)
        {
            throw new ApplicationException($"備份失敗: {ex.Message}", ex);
        }
        finally
        {
            if (sqlServer != null) sqlServer.DisConnect();
        }
    }

    /// 

    /// 資料庫還原 (支援異地還原/改名)
    /// 
    /// 目標資料庫名稱 (可異於來源)
    /// 備份檔路徑
    /// 備份集編號 (預設 1)
    /// 是否覆蓋現有資料庫
    /// 檔案重新配置 (邏輯名 → 實體路徑)
    public void RestoreDatabase(string targetDatabase, string backupPath,
                                int fileNumber = 1, bool replaceDatabase = true,
                                Dictionary moveFiles = null)
    {
        SQLServerClass sqlServer = null;
        RestoreClass restore = null;

        try
        {
            sqlServer = new SQLServerClass();
            sqlServer.LoginSecure = false;
            sqlServer.Connect(_server, _user, _pwd);

            restore = new RestoreClass();
            restore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            restore.Database = targetDatabase;
            restore.Files = backupPath;
            restore.FileNumber = fileNumber;
            restore.ReplaceDatabase = replaceDatabase;

            // 異地還原：指定資料/記錄檔新路徑
            if (moveFiles != null)
            {
                foreach (var kvp in moveFiles)
                {
                    restore.DatabaseFiles = kvp.Key;  // 邏輯檔名
                    restore.DatabaseFilePath = kvp.Value;  // 新實體路徑
                }
            }

            restore.SQLRestore(sqlServer);
        }
        catch (Exception ex)
        {
            throw new ApplicationException($"還原失敗: {ex.Message}", ex);
        }
        finally
        {
            if (sqlServer != null) sqlServer.DisConnect();
        }
    }
}

呼叫範例

var mgr = new SqlBackupManager("192.168.1.100", "sa", "StrongPwd123");

// 完整備份
mgr.BackupDatabase("Northwind", @"D:\Backups\Northwind_20240115.bak", 
                   "Northwind_Full", "每日完整備份");

// 異地還原為新資料庫
var moveMap = new Dictionary
{
    { "Northwind", @"E:\SQLData\Northwind_Restore.mdf" },
    { "Northwind_log", @"F:\SQLLog\Northwind_Restore.ldf" }
};
mgr.RestoreDatabase("Northwind_Restore", @"D:\Backups\Northwind_20240115.bak",
                    moveFiles: moveMap);

SQL-DMO vs T-SQL BACKUP/RESTORE 比較

遷移建議：SQL-DMO → SMO (現代專案)

新專案請直接使用 Microsoft.SqlServer.Management.Smo (NuGet: Microsoft.SqlServer.SqlManagementObjects)：

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

var server = new Server(new ServerConnection("192.168.1.100", "sa", "pwd"));
var backup = new Backup
{
    Action = BackupActionType.Database,
    Database = "Northwind",
    Devices = { new BackupDeviceItem(@"D:\Backups\Northwind.bak", DeviceType.File) },
    Initialize = true,
    Incremental = false
};
backup.SqlBackup(server);

常見錯誤排查