首页 > 解决方案 > 如何使用 SMO for .NET 恢复多个事务日志

问题描述

我正在开发一个数据库应用程序来恢复数据库及其事务日志。我正在使用 SQL Server 管理对象 (SMO) 库。

此应用程序的要求使我必须在单独的进程中恢复数据库备份文件及其事务日志。我可以毫不费力地恢复备份文件,但是,在恢复事务日志时,我遇到了一个问题:

        public void RestoreTransactionLogs(Server srv, DirectoryInfo filePath, DatabaseType dbType)
        {
            Restore res = new Restore()
            {
                Database = dbType.ToString(),
                Action = RestoreActionType.Log,
                ReplaceDatabase = false
            };

            FileInfo[] files = filePath.Parent.GetFiles("*.trn");

            foreach (FileInfo f in files)
            {
                res.Devices.AddDevice(f.FullName, DeviceType.File);
            }           

            try
            {
                res.SqlRestore(srv);
            }
            catch (SmoException ex)
            {
                Log.Fatal("An SMO Exception has occurred when restoring the database: " + dbType.ToString() + ": " + ex.Message);
                throw ex;
            }
            catch (Exception ex)
            {
                Log.Fatal("An exception has occurred when restoring the database:  " + dbType.ToString() + ": " + ex.Message);
                throw ex;
            }

        }

使用测试备份文件和 20 个事务日志,我遇到以下错误:

SmoException:System.Data.SqlClient.SqlError:“D:\Test Folder\testDatabase\log_00001.trn”上加载的媒体被格式化为支持 1 个媒体系列,但根据备份设备规范,预计有 20 个媒体系列。

我有一种感觉,我没有正确地将事务日志添加到我的设备集合中,或者我应该以不同的方式添加它们,但我不确定在哪里检查。MSDN 中有关事务日志的文档很少,我无法在网上找到太多。谢谢!

标签: c#.netsql-serversmo

解决方案


我认为你的问题是你不能只恢复一个事务日志。您必须先从完整备份开始,然后再应用事务日志。这必须在 db 处于无恢复状态时发生,因此在完全备份还原和事务日志还原之间不会发生对 db 的其他更改。另外,请记住,您必须按照事务日志的获取顺序应用它们。

这是我根据此处的 doco 示例所做的工作:

https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/backing-up-and-restoring-databases-and-transaction-logs?view=sql-服务器-ver15

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var server = new Server(new ServerConnection(connection));
    Database targetDb = server.Databases["TargetDbName"];

    // Make sure your user has ALTER ANY CONNECTION rights for this
    // not needed if you can be sure db is not in use
    server.KillAllProcesses(targetDb.Name);
    targetDb.SetOffline();

    Restore restoreDB = new Restore();
    restoreDB.Database = targetDb.Name;
    restoreDB.Action = RestoreActionType.Database;
    restoreDB.ReplaceDatabase = true;

    // Restore the full backup first
    var fullBackupDevice = new BackupDeviceItem("fullBackupFile.bak", DeviceType.File);
    restoreDB.Devices.Add(fullBackupDevice);
    restoreDB.NoRecovery = true;
    restoreDB.SqlRestore(server);
    restoreDB.Devices.Remove(fullBackupDevice);

    // Get the first taken transaction log file
    var firstTransactionBackupDevice = new BackupDeviceItem("firstTrnFile.trn", DeviceType.File);
    restoreDB.Devices.Add(firstTransactionBackupDevice);
    restoreDB.SqlRestore(server);
    restoreDB.Devices.Remove(firstTransactionBackupDevice);

    // Get the second taken transaction log file
    var secondTransactionBackupDevice = new BackupDeviceItem("secondTrnFile.trn", DeviceType.File);
    restoreDB.Devices.Add(secondTransactionBackupDevice);
    // You have to set this flag to false before the last file you will restore
    // to return the db to the normal state
    restoreDB.NoRecovery = false;
    restoreDB.SqlRestore(server);
    restoreDB.Devices.Remove(secondTransactionBackupDevice);

    targetDb.SetOnline();
    server.Refresh();
}

我知道您的问题有点老,您可能已经找到了解决方案,但希望这对其他人有所帮助。


推荐阅读