如何还原一批事务日志，而不是逐个还原

在SQL Server Management Studio中没有办法指定一堆事务日志备份（oк文件夹）来还原。

但是您可以在数据库MSDB（表备份集和相关的）中find有关SQL Server备份操作的所有信息。

这里是生成SQL Server命令的脚本，用于从备份恢复数据库，并应用从上次完整数据库备份执行的所有事务日志备份。 我认为它应该可以帮助你。

DECLARE @databaseName sysname DECLARE @backupStartDate datetime DECLARE @backup_set_id_start INT DECLARE @backup_set_id_end INT -- set database to be used SET @databaseName = '<your_database_name_here>' SELECT @backup_set_id_start = MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @databaseName AND type = 'D' SELECT @backup_set_id_end = MIN(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @databaseName AND type = 'D' AND backup_set_id > @backup_set_id_start IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999 SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH NORECOVERY' FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf WHERE b.media_set_id = mf.media_set_id AND b.database_name = @databaseName AND b.backup_set_id = @backup_set_id_start UNION SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH NORECOVERY' FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf WHERE b.media_set_id = mf.media_set_id AND b.database_name = @databaseName AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end AND b.type = 'L' UNION SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY' ORDER BY backup_set_id 

你只需要一个像SQL语句的列表…

 RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY GO RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN' GO 

所以你可以做一个VB脚本，它可以从给定的文件夹轻松地为你生成这个SQL。 这是一个例子http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/restoring-multiple-transaction-log-backu

一旦创build了SQL，你只需要检查它看起来是否正确并运行它。

下面是一个关于如何使用TSQL从目录中的备份文件中自动生成SQL Server还原脚本的示例：

从目录中的备份文件自动生成SQL Server恢复脚本

自动生成SQL Server数据库恢复脚本

我不想使用接受的答案的基于SQL的方法，因为我不想启用扩展存储过程。 所以我写了一个PowerShell脚本来做到这一点。

您将其指向一个文件夹，并根据最新的完整备份和所有后续事务日志备份生成脚本。

  [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") $foldername = New-Object System.Windows.Forms.FolderBrowserDialog $foldername.rootfolder = "MyComputer" $foldername.ShowNewFolderButton = $false $foldername.SelectedPath = "E:\DatabaseBackups" if($foldername.ShowDialog() -eq "OK") { $backupPath = Get-Item($foldername.SelectedPath) $databaseName = $backupPath.Name Write-Host($backupPath) Write-Host($databaseName) $transactionLogFiles = New-Object System.Collections.ArrayList; $outputFile = "Restore Database - Script.sql" $backupFile; foreach ($file in get-childitem ($backupPath) | sort-object LastWriteTime -descending) { if ($file.Extension -eq '.trn') { [void]$transactionLogFiles.Add($file); } elseif ($file.Extension -eq '.bak') { $backupFile = $file; break; } } Set-Content $outputFile "" Add-Content $outputFile "USE master" Add-Content $outputFile "ALTER DATABASE $databaseName SET SINGLE_USER WITH ROLLBACK AFTER 5" Add-Content $outputFile "RESTORE DATABASE $databaseName FROM DISK = '$($backupFile.FullName)' WITH NORECOVERY"; foreach ($file in $transactionLogFiles | sort-object LastWriteTime) { Add-Content $outputFile "RESTORE LOG $databaseName FROM DISK = '$($file.FullName)' WITH NORECOVERY"; } Add-Content $outputFile "RESTORE DATABASE $databaseName WITH RECOVERY"; Add-Content $outputFile "ALTER DATABASE $databaseName SET MULTI_USER"; Add-Content $outputFile "USE $databaseName" Write-Host("Script generated at $outputFile"); Write-Host "Press any key to continue ..." $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") Invoke-Item $outputFile }