萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

C# 調用 T-SQL 實現 SQL Server 備份/還原(完整/差異/殺進程)

核心類:DbBackupRestore

public class DbBackupRestore
{
    private readonly string _connStr;

    public DbBackupRestore(string connStr) => _connStr = connStr;

    /// 完整/差異備份
    /// .bak 完整路徑
    /// true=差異,false=完整
    /// 命令超時秒數
    public bool Backup(string bakFile, bool diff = false, int timeoutSec = 600)
    {
        if (diff && !File.Exists(bakFile)) return false; // 差異備份需基於現有完整備份

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

        return ExecuteNonQuery(sql, timeoutSec);
    }

    /// 還原數據庫(強制覆蓋、殺掉連接)
    public bool Restore(string bakFile, int timeoutSec = 600)
    {
        var dbName = ExtractDbName(_connStr);
        var masterConn = _connStr.Replace($"Database={dbName};", "Database=master;");

        // 1. 殺掉所有用戶連接
        var killSql = $@"
            DECLARE @sql NVARCHAR(MAX) = N'';
            SELECT @sql += 'KILL ' + CAST(SPID AS VARCHAR) + ';'
            FROM MASTER..SYSPROCESSES
            WHERE DBID = DB_ID(N'{dbName}') AND SPID <> @@SPID;
            EXEC (@sql);";
        ExecuteNonQueryOnMaster(killSql, masterConn, 30);

        // 2. 執行還原
        var restoreSql = $"RESTORE DATABASE [{dbName}] FROM DISK = N'{bakFile}' WITH REPLACE, STATS = 10;";
        return ExecuteNonQueryOnMaster(restoreSql, masterConn, timeoutSec);
    }

    private string ExtractDbName(string connStr)
    {
        // 簡易解析 "Database=XXX;" 或 "Initial Catalog=XXX;"
        var match = Regex.Match(connStr, @"(?:Database|Initial Catalog)\s*=\s*([^;]+)", RegexOptions.IgnoreCase);
        return match.Success ? match.Groups[1].Value.Trim() : throw new ArgumentException("連接字符串缺少 Database");
    }

    private bool ExecuteNonQuery(string sql, int timeout)
        => ExecuteNonQueryOnMaster(sql, _connStr, timeout);

    private bool ExecuteNonQueryOnMaster(string sql, string connStr, int timeout)
    {
        try
        {
            using (var cn = new SqlConnection(connStr))
            using (var cmd = new SqlCommand(sql, cn) { CommandTimeout = timeout })
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                return true;
            }
        }
        catch (Exception ex)
        {
            // 記錄日志 ex.ToString()
            return false;
        }
    }
}

關鍵點解析

  • 差異備份前置條件:必須存在最近的完整備份文件,否則報錯。
  • COMPRESSION:SQL 2008+ 支持備份壓縮,大幅減小文件、提速 I/O。
  • STATS = 10:每 10% 進度向客戶端發送消息,便於 UI 進度條。
  • 殺進程邏輯:在 master 上下文動態拼接 KILL SPID,避免「數據庫正在使用」導致還原失敗。
  • REPLACE:允許覆蓋現有數據庫文件,配合殺進程實現強制還原。
  • CommandTimeout:大庫備份/還原動輒數十分鐘,必須顯式設置(如 600 秒)。

調用示例

var svc = new DbBackupRestore(ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);
bool ok = svc.Backup(@"D:\Backup\MyDB_Full_20240518.bak", diff: false);
ok = svc.Backup(@"D:\Backup\MyDB_Diff_20240518_1400.bak", diff: true);
ok = svc.Restore(@"D:\Backup\MyDB_Full_20240518.bak");
copyright © 萬盛學電腦網 all rights reserved