ASP.NET 備份還原 SQL Server 資料庫：SMO 程式化操作與排程自動化

Web 應用需內建資料庫備份/還原功能，支援手動觸發、定時排程、異地複製。本文使用 SQL Server Management Objects (SMO) 程式化操作，避免 BACKUP DATABASE T-SQL 拼接注入風險，支援壓縮、加密、差異備份。

核心類別：DatabaseBackupHelper

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

public class DatabaseBackupHelper {
    private readonly string _connectionString;
    public DatabaseBackupHelper(string connectionString) { _connectionString = connectionString; }

    public void Backup(string databaseName, string backupFilePath, 
        BackupAction action = BackupAction.Database, bool compress = true, string password = null) {
        var server = new Server(new ServerConnection(new SqlConnection(_connectionString)));
        var backup = new Backup {
            Action = action,
            Database = databaseName,
            CompressionOption = compress ? BackupCompressionOptions.On : BackupCompressionOptions.Off,
            Devices = { new BackupDeviceItem(backupFilePath, DeviceType.File) },
            Initialize = true, // 覆蓋現有檔案
            SkipTapeHeader = true,
            FormatMedia = false
        };
        if (!string.IsNullOrEmpty(password)) {
            backup.MediaPassword = password; // 備份集加密 (SQL 2014+)
        }
        backup.SqlBackup(server);
    }

    public void Restore(string databaseName, string backupFilePath, 
        bool replace = true, string recoveryMode = "NORECOVERY") {
        var server = new Server(new ServerConnection(new SqlConnection(_connectionString)));
        var restore = new Restore {
            Database = databaseName,
            Action = RestoreActionType.Database,
            Devices = { new BackupDeviceItem(backupFilePath, DeviceType.File) },
            ReplaceDatabase = replace,
            NoRecovery = recoveryMode == "NORECOVERY",
            RecoveryCompletion = recoveryMode == "RECOVERY" ? RecoveryCompletion.Recovery : RecoveryCompletion.NoRecovery
        };
        // 關閉現有連線
        server.KillAllProcesses(databaseName);
        restore.SqlRestore(server);
    }
}

SMO 組件引用 (NuGet)

Install-Package Microsoft.SqlServer.SqlManagementObjects

Web 介面整合：非同步執行與進度回報

public class BackupController : Controller {
    private readonly DatabaseBackupHelper _helper;
    public BackupController() { _helper = new DatabaseBackupHelper(ConfigurationManager.ConnectionStrings["Default"].ConnectionString); }

    [HttpPost]
    public async Task<ActionResult> Backup(string dbName) {
        var fileName = $"{dbName}_{DateTime.Now:yyyyMMdd_HHmmss}.bak";
        var path = Path.Combine(Server.MapPath("~/Backups"), fileName);
        Directory.CreateDirectory(Path.GetDirectoryName(path));

        // 長時間操作放入背景工作 (Hangfire / HostedService / Queue)
        await Task.Run(() => _helper.Backup(dbName, path, compress: true));
        
        // 可選：上傳至 Azure Blob / AWS S3 / FTP
        return Json(new { success = true, file = fileName });
    }

    [HttpPost]
    public async Task<ActionResult> Restore(string dbName, string backupFile) {
        var path = Path.Combine(Server.MapPath("~/Backups"), backupFile);
        if (!System.IO.File.Exists(path)) return Json(new { success = false, msg = "檔案不存在" });
        await Task.Run(() => _helper.Restore(dbName, path, replace: true));
        return Json(new { success = true });
    }
}

排程自動化：SQL Server Agent / Hangfire / Windows Service

進階備份策略

• 完整 + 差異 + 交易記錄：每日完整、每小時差異、每 15 分鐘記錄，RPO < 15 分鐘。
• 備份驗證：RESTORE VERIFYONLY 或還原至測試環境跑一致性檢查 (DBCC CHECKDB)。
• 異地備份：備份完成後自動複製至異地儲存 (AzCopy / rclone / S3 複製)。
• 加密：BACKUP ... WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = ...)，SMO backup.EncryptionAlgorithm。
• 保留策略：保留最近 7 天完整、4 週週末完整、12 月月末完整，自動清理過期檔案。

權限最小化原則

• Web 應用連線帳號僅需 db_backupoperator、db_owner (還原需) 角色。
• 禁止使用 sa 或系統管理員帳號執行備份/還原。
• 備份檔案儲存路徑 ACL 僅授予服務帳號讀寫。