C# 呼叫 T-SQL 實作 SQL Server 完整/差異備份與還原：含逾時控制與單一使用者模式

備份/還原屬長時間操作，需設定 CommandTimeout 避免中斷。本文提供可直接落地的 C# 封裝，涵蓋完整備份、差異備份、還原前殺進程、單一使用者模式切換。

核心類別架構

public class SqlBackupRestore
{
    private readonly string _connStr;
    public SqlBackupRestore(string connStr) => _connStr = connStr;

    // 完整備份 / 差異備份
    public bool BackupDatabase(string filePath, bool isDifferential)
    {
        if (isDifferential && !File.Exists(filePath)) return false; // 差異需有基礎完整備份

        var dbName = ExtractDbName(_connStr);
        var sql = isDifferential
            ? $"BACKUP DATABASE [{dbName}] TO DISK = N'{filePath}' WITH DIFFERENTIAL, COMPRESSION, STATS = 5;"
            : $"BACKUP DATABASE [{dbName}] TO DISK = N'{filePath}' WITH COMPRESSION, STATS = 5;";

        return ExecuteNonQuery(sql, 3600); // 1 小時逾時
    }

    // 還原（含殺進程、單一使用者模式）
    public bool RestoreDatabase(string filePath)
    {
        var dbName = ExtractDbName(_connStr);
        var masterConn = _connStr.Replace($"Database={dbName}", "Database=master");

        // 1. 切換到 master 並殺掉所有連線
        var killSql = $@"
            ALTER DATABASE [{dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        ";
        ExecuteNonQueryOnMaster(killSql, 60);

        // 2. 還原
        var restoreSql = $@"
            RESTORE DATABASE [{dbName}] FROM DISK = N'{filePath}' 
            WITH REPLACE, RECOVERY, STATS = 5;";
        var ok = ExecuteNonQueryOnMaster(restoreSql, 3600);

        // 3. 還原多使用者模式
        if (ok) ExecuteNonQueryOnMaster($"ALTER DATABASE [{dbName}] SET MULTI_USER;", 30);

        return ok;
    }

    private string ExtractDbName(string connStr)
    {
        var parts = connStr.Split(';');
        var dbPart = parts.FirstOrDefault(p => p.TrimStart().StartsWith("Database=", StringComparison.OrdinalIgnoreCase)
                                            || p.TrimStart().StartsWith("Initial Catalog=", StringComparison.OrdinalIgnoreCase));
        return dbPart?.Split('=')[1].Trim() ?? throw new ArgumentException("Connection string missing Database/Initial Catalog");
    }

    private bool ExecuteNonQuery(string sql, int timeoutSec)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand(sql, conn) { CommandTimeout = timeoutSec };
        conn.Open();
        cmd.ExecuteNonQuery();
        return true;
    }

    private bool ExecuteNonQueryOnMaster(string sql, int timeoutSec)
    {
        var masterConnStr = _connStr.Replace($"Database={ExtractDbName(_connStr)}", "Database=master");
        using var conn = new SqlConnection(masterConnStr);
        using var cmd = new SqlCommand(sql, conn) { CommandTimeout = timeoutSec };
        conn.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
}

關鍵細節解析

進階補充：非同步與進度回報

生產環境建議改用 ExecuteNonQueryAsync 搭配 SqlCommand.StatementCompleted 事件或 BACKUP/RESTORE ... WITH STATS 透過 SqlConnection.InfoMessage 事件即時推送進度至 UI。