SQL Server Utilities - Find the specific sql server t-log to be restored for an unrecovered database

Recently when I needed to find the Transaction Log file that can be applied to a log shipping secondary database I decided to take a few moments and automate the process that I usually do in the old fashion manual way and that includes:

1. generating the restore log commands using a query over backupset joining backupmediafamily

2. Find the specific file by eliminating half of the files and so on repeatedly until the file is found (say you have a few hundred of files all in all, you split them by half roughly and try to restore a log, based on the output of the failed restore command you now know if you need an older file or a more recent file, so now you are in a position that you got rid of one half of the files and you can split the remaining half again and so on. Very quickly you reach the desired file).

3. Run a script with all the required restore log commands

When thinking about automating the process I knew what is needed

1. Get the oldest redo_start_lsn from sys.mater_files of the database in discussion (this is known as the database lsn )

2. Issue a RESTORE HEADERONLY command for each t-logs and get the FirstLsn and LastLsn from the backupset

3. For each t-log compare to see if the following condition is met: redo_start_lsn >= FirstLsn and the redo_start_lsn < LastLsn

While started to code this logic I came across a documented system stored procedure named sys.sp_can_tlog_be_applied I was not familiar nor knew of its existence that how surprisingly… does just what I needed.

The procedure executes the RESTORE HEADERONLY command and applies the logic described above. Of course there is the advantage of using the procedure over a self-deployed logic since changes to the product such as adding a column to the output of the RESTORE HEADERONLY command will not break the code etc.

With that information in hand all that I was left to do is iterate through the files in the folder while executing the procedure sys.sp_can_tlog_be_applied per each file and capture the returned output variable until a value of 1 is received indicating that this is the t-log file that can be restored.

I preferred to accomplish that task in PowerShell but of course you can accomplish that using t-sql remaining in SSMS but doing so you will need to get the information of the files in the folder. This can be done using SQLServerIO from David Wiseman’s website or using other ways such as xp_cmdshell calling the dir command with the appropriate switches, and there are other ways.

#region <variables>
[bool]$user_interactive = [Environment]::UserInteractive;

[string]$server = $env:COMPUTERNAME;
[string]$command_type = 'Scalar';
[string]$database = 'master';
[bool]$windows_authentication = $true

if(-not $windows_authentication)
{
    [string]$sql_user = 'sqluser';
    [string]$sql_password = 'sqlpassword';
}


[string]$log_shipping_database = 'some_database';
[string]$path = 'B:\tlogs\'; # path lt log files
#endregion



<#
.Synopsis
   Executes sql command
.DESCRIPTION
   A generic code to execute sql commands
.EXAMPLE
   ExecuteScalar
        $val = Exec-Sql $server $database $command_text $command_type $integrated_security;
   DataSet        
        $ds = Exec-Sql $server $database $command_text $command_type $integrated_security;
#>
function Exec-Sql
{
    [CmdletBinding()]
    [Alias()]
    #[OutputType([int])]
    Param
    (    
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=0)]
        [string]$Server,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=1)]
        [string]$Database,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=2)]
        [string]$CommandText,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=3)]
        [string]$CommandType,       

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=4)]
        [bool]$IntegratedSecurity,  
        
        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=5)]
        [string]$User,

        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=6)]
        [string]$Password,
        
        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=7)]
        [int32]$CommandTimeOut = 30,

        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=8)]
        [string]$ApplicationName = "CloudMonitoring"    
    )

    Begin
    {
        if($IntegratedSecurity -eq $true)
        {
            $ConnectionString = "Server=$Server; Database=$Database; Integrated Security=$IntegratedSecurity; Application Name=$ApplicationName;";
        }
        else
        {
            # Validate the credentials were supplied
            if( [string]::IsNullOrEmpty($User)) {Throw 'The ''$User'' variable does not contain a valid value'}
            if( [string]::IsNullOrEmpty($Password)) {Throw 'The ''$Password'' variable does not contain a valid value'}

            $ConnectionString = "Server=$Server; Database=$Database; Integrated Security=$IntegratedSecurity; User=$User; Password=$Password; Application Name=$ApplicationName;";
        }
        #Write-Host $ConnectionString -ForegroundColor Yellow

        if($CommandType -notin ('NonQuery' ,'Scalar' ,'DataSet') )
        {
            throw 'The ''$CommandType'' parameter contains an invalid value Valid values are: ''NonQuery'' ,''Scalar'' ,''DataSet''';
        }

        try
        {
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
            $SqlCommand = $sqlConnection.CreateCommand();
            $SqlConnection.Open(); 
            $SqlCommand.CommandText = $CommandText;                      
                  
            # NonQuery
            if($CommandType -eq 'NonQuery')
            {      
                $sqlCommand.ExecuteNonQuery();
                return;
            }

            # Scalar
            if($CommandType -eq 'Scalar')
            {                
                 Write-Host $CommandText -ForegroundColor Yellow   
                                   
                $Val = $sqlCommand.ExecuteScalar();
                return $Val;
            }
            
            # DataSet
            if($CommandType -eq "DataSet")
            {
                $DataSet = New-Object System.Data.DataSet;
                $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
                $SqlDataAdapter.SelectCommand = $SqlCommand;    
                $SqlDataAdapter.Fill($DataSet);  
                return $DataSet;   
            }
        }
        catch
        {       
            throw;
        }
    }
}


if (-not (Test-Path -Path $path))
{     
    if ($user_interactive) {Write-Host -ForegroundColor Red 'The provided path does not exists. Terminating.'; };
    return;    
}


# Get the log files
$files = Get-ChildItem $path -recurse -include *.trn | Where-Object {$_.PSIsContainer -eq $False; } 

if ($user_interactive ) {Write-Host -ForegroundColor Green $server; };
try
{   
    # Itereate over the files and execute the procedure 'sys.sp_can_tlog_be_applied' per each file
    foreach($file in $files)
    {
        if($file.Name -notlike '*.trn*') {continue} # If this is not a trn file skip it
        
        [string]$query = 'DECLARE @val int = 0; EXEC sys.sp_can_tlog_be_applied @backup_file_name = ''' + $file.FullName  + ''', @database_name = ''' + $log_shipping_database + ''',@result = @val output; SELECT @val';


        if ($user_interactive ) {Write-Host -ForegroundColor Yellow $query};
        if($windows_authentication ) 
        { 
            $val = Exec-Sql $server $database $query $command_type $true; 
        }
        else
        {
            $val = Exec-Sql $server $database $query $command_type $false $sql_user $sql_password;           
        } 

        
        # Stop once we reached the file that can be restored.
        if ($val -eq 1)
        {               
            [string]$command = 'RESTORE DATABASE [' + $log_shipping_database + '] FROM disk = ''' + $file.FullName + ''' WITH NORECOVERY /*REPLACE*/ ;';
            if ($user_interactive ) {Write-Host -ForegroundColor Green $command; };  
            if ($user_interactive ) {Write-Host -ForegroundColor Green 'The file: '  $file.FullName ' can be restored. Terminating.'; };  
            return;            
        }    
    }                 
}
catch [Exception] 
{
    $exception = $_.Exception;
    if ($user_interactive -eq $true) {Write-Host -ForegroundColor Red $exception;}         
}