如何使用的SMO恢复多个事务日志.NET

Question

我正在开发一个数据库应用程序，以恢复带有事务日志的数据库。我使用的是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：系统。数据SqlClient。SqlError:媒体加载于"D:\Test Folder\testDatabase\log_00001.trn"的格式为支持1个媒体家族，但根据备份设备规范。

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

Answer 1

我认为您的问题是无法仅恢复事务日志。您必须先从完整备份开始，然后应用事务日志。这必须发生在数据库未处于恢复状态时，因此在完全备份还原和事务日志还原之间不会对数据库进行其他更改。此外，请记住，您必须按照事务日志的获取顺序应用它们。

以下是我基于这里的doco示例所做的工作：

https://learn.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();
}

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

Answer 2

大家好，您可以在sql server中尝试以下脚本。请更改目录文件路径。还要研究"有恢复"one_answers"没有恢复"。。示例

RESTORE DATABASE [DW] FROM DISK = 'G:MSSQLDataFullBackupsdb.bak' WITH NORECOVERY
Go
RESTORE DATABASE [DW] FROM DISK = 'G:MSSQLDataDifferentialBackupsdb.bak' WITH NORECOVERY
--repeat how many ever times if multiple based on time DB crashed.
GO
RESTORE DATABASE [DW] FROM DISK = 'G:MSSQLDataTransactionLogsdb.bak' WITH NORECOVERY
--Final T-Log to exact point in time recovery.
RESTORE DATABASE [DW] FROM DISK = 'G:MSSQLDataTransactionLogsdb.bak' WITH RECOVERY