SQLDBA - Script to Loop through all the transaction log file and restore them automatically.

 

Script to Loop through all the transaction log file and restore them automatically.

Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup.

Method1:

USE master;

DECLARE @t AS TABLE (fname VARCHAR(max));

INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn';

SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL

Method2:

Powershell  Script;

To automatically loop through and restore all SQL Server transaction log files (.trn), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence.

# Define variables
$serverName = "YourSQLServerInstance"  
$databaseName = "YourDatabase"  
$backupFolder = "C:\Backups\Logs"
$restoreDatabase = $false  # Set to $true if you want to restore the full DB first

# Optional: Restore full backup if needed
if ($restoreDatabase) {
    $fullBackupFile = "C:\Backups\YourDatabase.bak"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE DATABASE [$databaseName]
        FROM DISK = N'$fullBackupFile'
        WITH NORECOVERY, REPLACE"
}

# Get all .trn files sorted by creation time
$logFiles = Get-ChildItem -Path $backupFolder -Filter *.trn | Sort-Object LastWriteTime

# Loop through and restore each log file
foreach ($file in $logFiles) {
    Write-Host "Restoring log file: $($file.Name)"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE LOG [$databaseName]
        FROM DISK = N'$($file.FullName)'
        WITH NORECOVERY"
}

# Finally, recover the database
Invoke-Sqlcmd -ServerInstance $serverName -Query "
    RESTORE DATABASE [$databaseName] WITH RECOVERY"

Write-Host "Transaction log restore completed."

Method3:

# Define parameters
$serverInstance = "localhost\SQLEXPRESS"  # Change to your SQL Server instance
$databaseName = "YourDatabaseName"        # Change to your target database
$logBackupFolder = "C:\LogBackups"        # Folder containing .trn files

# Load SQL Server SMO
Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"

# Connect to SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance

# Get all .trn files sorted by name (assuming naming reflects order)
$logFiles = Get-ChildItem -Path $logBackupFolder -Filter *.trn | Sort-Object Name

foreach ($logFile in $logFiles) {
    Write-Host "Restoring log file: $($logFile.Name)"

    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Action = "Log"
    $restore.Database = $databaseName
    $restore.Devices.AddDevice($logFile.FullName, "File")
    $restore.NoRecovery = $true  # Keep in restoring state for next log

    try {
        $restore.SqlRestore($server)
        Write-Host "Successfully restored: $($logFile.Name)"
    } catch {
        Write-Host "Failed to restore: $($logFile.Name)"
        Write-Host $_.Exception.Message
    }
}

Write-Host "All log files processed."