How do I restore a batch of transaction logs rather than one by one
Solution 1
There is no ways to specify bunch of transaction log backups (oк folder) to restore in SQL Server Management studio.
But you can find all information about SQL Server backup operations in database MSDB (table backupset and related).
Here is script to generate SQL Server commands for restore database from backup and applying all transaction logs backups performed from last full database backup. I think it should help you.
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
Solution 2
Here is an example on how you can auto generate SQL Server restore script from backup files in a directory using TSQL:
Auto generate SQL Server restore script from backup files in a directory
Auto generate SQL Server database restore scripts
Solution 3
you just need a list of sql statements like...
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO
So you can make a VB script which easily generates this SQL for you from a given folder. Here is an example http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/restoring-multiple-transaction-log-backu
Once it has created the SQL you just need to check that it looks right and run it.
Solution 4
I didn't want to use the SQL based approach of the accepted answer, because I didn't want to enable extended stored procedures. So I wrote a powershell script to do it.
You point it at a folder, and it generates a script based on the most recent full backup, and all subsequent transaction log backups.
[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
}
Related videos on Youtube
aSkywalker
Updated on September 17, 2022Comments
-
aSkywalker over 1 year
I have a SQL Server database that backs up the transaction logs every 10 minutes, with an overnight full backup.
Using SQL 2008 Management studio we seem to have to select each transaction log one by one. Is there any way to point it at a directory?
I am considering running a differential backup several times a day, which may offset some of this, but going one by one through dozens/hundreds of files seems to be pretty time consuming. Writing code to try to script it seems far too off topic from our core competencies.
If SQL Server Management Studio does not have a faster way, perhaps there is a third party tool available?
-
Jason Clark about 8 yearsyes, if all the possible mechanism doesn't works then its better to take help from SQL Log Recovery Tool sqlserverlogexplorer.com/restore
-
-
realMarkusSchmidt about 10 yearsWorks really great if you can run this script on the original server but want to restore on another server!
-
Andrew Savinykh about 9 yearsThe script comes from here mssqltips.com/sqlservertip/1243/…
-
agrath over 6 yearsThank you! You just saved my bacon from a hot hot fire... msdb was corrupt so had to restore that from backup and didn't have the log chain info. Your script saved me from having to build the transaction log restore scripts by hand based on filename!
-
Mitch Wheat about 6 years@sergey: you should attribute scripts you lift from web! : mssqltips.com/sqlservertip/1243/…
-
Ben Curthoys over 5 yearsThat's what it does. One database (the most recent) and all the transaction logs since then. There's no point looking at transaction logs from before the full backup.