Restore date in scripts need to add (concatenate) the last date modified to the .trn

SQL Server Administration
1

Hello,

We are restoring MSSQL Transaction Logs and the scenario I ran into Wednesday was the file date gets used in our script
to create the RESTORE script and the vendor keeps getting them out of sorts.

So, we need to add something that uses the last 6 digits plus .trn because it skips the one out of order based on the
LSN restores the correct LSN, but then everything after it fails, because it needs the one it skipped to match up.

I created this shortened snapshot to protect database names: Of course I have a picture with some more files this is a shortened version.

Name Date Modified Type Size
ECW30BCL/TRG2A_db_xxxxx_LOG_0210704_23005.trn 7/5/2021 12:29 AM trn file 5,013,982 Kb
file name structured like above.trn 7/5/2021 12:29 AM trn file 5,487 Kb
file name structured like above.trn 7/5/2021 12:30 AM trn file 18 Kb

Power Shell Script:

System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")

$foldername = New-Object System.Windows.Forms.FolderBrowserDialog
$foldername.rootfolder = "MyComputer"
$foldername.ShowNewFolderButton = $false
$foldername.SelectedPath = "D:\db_xxxxxxx"

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"
   

    foreach ($file in $transactionLogFiles | sort-object LastWriteTime)
    {
        Add-Content $outputFile "RESTORE LOG $databaseName FROM DISK = '$($file.FullName)' WITH NORECOVERY";   
    }
   

    Write-Host("Script generated at $outputFile");
    Invoke-Item $outputFile

Any help and suggestions will be greatly appreciated.

Thanks,

Kurt