核心類: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");