程式化備份與還原 SQL Server 資料庫,傳統使用 **SQL-DMO** (SQL 2000/2005),現代改用 **SMO (SQL Server Management Objects)**。本文提供兩者核心程式碼,並說明 Access 資料庫建立/壓縮的 JRO/ADOX 用法。
NuGet:`Install-Package Microsoft.SqlServer.SqlManagementObjects`
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
public void BackupDatabase(string serverName, string dbName, string backupPath,
string user = null, string password = null)
{
var conn = new ServerConnection(serverName)
{
LoginSecure = string.IsNullOrEmpty(user), // true=Windows 驗證
Login = user,
Password = password
};
var server = new Server(conn);
var backup = new Backup
{
Action = BackupActionType.Database,
Database = dbName,
BackupSetName = $"{dbName}_Backup_{DateTime.Now:yyyyMMddHHmmss}",
BackupSetDescription = "自動備份",
CompressionOption = BackupCompressionOptions.On, // SQL 2008+ 支援壓縮
Initialize = true, // 覆蓋現有備份集
FormatMedia = false
};
backup.Devices.AddDevice(backupPath, DeviceType.File);
backup.SqlBackup(server); // 執行備份
}
public void RestoreDatabase(string serverName, string dbName, string backupPath,
string user = null, string password = null, bool replace = true)
{
var conn = new ServerConnection(serverName) { LoginSecure = string.IsNullOrEmpty(user), Login = user, Password = password };
var server = new Server(conn);
// 確保資料庫不在使用中 (設為單人模式強制斷開連線)
if (server.Databases.Contains(dbName))
{
var db = server.Databases[dbName];
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
db.Alter(TerminationClause.RollbackTransactionsImmediately);
}
var restore = new Restore
{
Action = RestoreActionType.Database,
Database = dbName,
ReplaceDatabase = replace, // WITH REPLACE
NoRecovery = false,
FileNumber = 1
};
restore.Devices.AddDevice(backupPath, DeviceType.File);
// 重新定位檔案 (避免路徑衝突)
var files = restore.ReadFileList(server);
foreach (DataRow row in files.Rows)
{
string logicalName = row["LogicalName"].ToString();
string physicalName = row["PhysicalName"].ToString();
string newPath = Path.Combine(server.MasterDBPath, Path.GetFileName(physicalName));
restore.RelocateFiles.Add(new RelocateFile(logicalName, newPath));
}
restore.SqlRestore(server);
// 還原後設回多使用者模式
if (server.Databases.Contains(dbName))
{
var db = server.Databases[dbName];
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
db.Alter();
}
}
需註冊 `SQLDMO.dll` (COM),`using SQLDMO;`
// 備份
SQLDMO.Backup backup = new SQLDMO.BackupClass();
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
sqlServer.LoginSecure = false;
sqlServer.Connect(server, login, pass);
backup.Database = dbName;
backup.Files = backupPath;
backup.BackupSetName = dbName;
backup.Initialize = true;
backup.SQLBackup(sqlServer);
sqlServer.DisConnect();
// 還原
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restore.Database = dbName;
restore.Files = backupPath;
restore.FileNumber = 1;
restore.ReplaceDatabase = true;
restore.SQLRestore(sqlServer);
// 需參考:Microsoft ADO Ext. 2.8 for DDL and Security (msadox.dll)
// Microsoft Jet and Replication Objects 2.6 (msjro.dll)
using ADOX; // 建立
using JRO; // 壓縮
// 建立 .mdb
public void CreateAccessMdb(string filePath)
{
if (File.Exists(filePath)) throw new Exception("檔案已存在");
var cat = new CatalogClass();
cat.Create($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};");
}
// 壓縮修復 .mdb (需獨佔)
public void CompactAccessMdb(string srcPath, string destPath)
{
var engine = new JetEngine();
string srcConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={srcPath}";
string destConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={destPath}";
engine.CompactDatabase(srcConn, destConn);
// 覆蓋原檔
File.Copy(destPath, srcPath, true);
File.Delete(destPath);
}
| 技術 | 適用版本 | 部署需求 | 現代建議 |
|---|---|---|---|
| SMO | SQL 2005+ | NuGet 套件 (含原生 DLL),x64/x86 自動處理 | ✅ 首選 |
| SQL-DMO | SQL 2000/2005 | COM 註冊、32 位元限制、已淘汰 | ❌ 僅維護極舊系統 |
| T-SQL BACKUP/RESTORE | 所有版本 | 無額外組件,`SqlCommand.ExecuteNonQuery` 執行 | ✅ 最輕量、最高效、無相依 |
| ADOX/JRO | Access .mdb | Access Database Engine 安裝、32 位元 | ⚠️ 僅限 Access 維護 |
public void BackupDbTsql(string connStr, string dbName, string backupFile)
{
using (var conn = new SqlConnection(connStr))
{
conn.Open();
// 確保備份目錄存在
string sql = $@"BACKUP DATABASE [{dbName}] TO DISK = N'{backupFile}'
WITH NOFORMAT, NOINIT, NAME = N'{dbName}-Full Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10";
using (var cmd = new SqlCommand(sql, conn)) cmd.ExecuteNonQuery();
}
}
public void RestoreDbTsql(string connStr, string dbName, string backupFile)
{
using (var conn = new SqlConnection(connStr))
{
conn.Open();
// 切換到 master 執行還原
string sql = $@"ALTER DATABASE [{dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [{dbName}] FROM DISK = N'{backupFile}'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
ALTER DATABASE [{dbName}] SET MULTI_USER;";
using (var cmd = new SqlCommand(sql, conn)) cmd.ExecuteNonQuery();
}
}
現代 .NET 應用操作 SQL Server 備份還原,**首選 T-SQL BACKUP/RESTORE 指令**,次選 SMO 物件模型 (需複雜邏輯時),棄用 SQL-DMO。Access 僅在無法遷移時使用 JRO 壓縮。