萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET SQL Server 資料庫備份與還原 (SQL-DMO / SMO) 完整實作

程式化備份與還原 SQL Server 資料庫,傳統使用 **SQL-DMO** (SQL 2000/2005),現代改用 **SMO (SQL Server Management Objects)**。本文提供兩者核心程式碼,並說明 Access 資料庫建立/壓縮的 JRO/ADOX 用法。

現代方案:SMO (Microsoft.SqlServer.Management.Smo)

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();
    }
}

傳統方案:SQL-DMO (僅供舊系統維護參考)

需註冊 `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);

Access 資料庫建立與壓縮 (ADOX / JRO)

// 需參考: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);
}

關鍵比較與選擇建議

技術適用版本部署需求現代建議
SMOSQL 2005+NuGet 套件 (含原生 DLL),x64/x86 自動處理✅ 首選
SQL-DMOSQL 2000/2005COM 註冊、32 位元限制、已淘汰❌ 僅維護極舊系統
T-SQL BACKUP/RESTORE所有版本無額外組件,`SqlCommand.ExecuteNonQuery` 執行✅ 最輕量、最高效、無相依
ADOX/JROAccess .mdbAccess Database Engine 安裝、32 位元⚠️ 僅限 Access 維護

最佳實踐:直接執行 T-SQL (最簡潔、無相依)

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();
    }
}

生產環境備份策略建議

  1. 維護計畫 (Maintenance Plan) / SQL Agent Job: 排程完整/差異/記錄備份,非程式碼控制。
  2. 程式碼觸發: 僅用於使用者手動「立即備份」按鈕、部署前自動備份、測試環境快速還原。
  3. 備份加密: `WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = ...)`
  4. 異地備份: 備份完成後自動複製至異地儲存 (Azure Blob, AWS S3, NAS)。
  5. 還原演練: 定期在測試環境執行還原驗證備份檔有效性。

現代 .NET 應用操作 SQL Server 備份還原,**首選 T-SQL BACKUP/RESTORE 指令**,次選 SMO 物件模型 (需複雜邏輯時),棄用 SQL-DMO。Access 僅在無法遷移時使用 JRO 壓縮。

copyright © 萬盛學電腦網 all rights reserved