Functions/Get-SdtBackupHistory.ps1

function Get-SdtBackupHistory {
<#
    .SYNOPSIS
    This function returns database backup history required to perform database restore
    .DESCRIPTION
    This function returns database backup history for performing either latest restore or point in time recovery.
    It accepts SqlInstance, databases to include/exclude and StopAtTime for point in time recovery.
    .PARAMETER SqlInstance
    Name of SqlInstance where backup history is to be searched
    .PARAMETER Database
    Databases for which backup history is required
    .PARAMETER ExcludeDatabase
    Databases which should be excluded from backup history
    .PARAMETER BackupType
    Type of backup required. Choices include 'Full', 'Diff' and 'Log'
    .PARAMETER StopAtTime
    Point in time for restoring Log Backups
    .EXAMPLE
    Get-SdtBackupHistory -SqlInstance 'testvm' -Database 'db01','Db02' -BackupType Log;
    This command return latest set of Full, Diff & Log for databases db01 and db02 from server testvm
    .EXAMPLE
    Get-SdtBackupHistory -SqlInstance 'testvm' -ExcludeDatabase 'db01','Db02' -BackupType Full;
    This command return latest set of Full bacukps for all databases except db01 and db02 from server testvm
#>

    [CmdletBinding()]
    Param (
        [String]$SqlInstance,
        [String[]]$Database,
        [String[]]$ExcludeDatabase,
        [ValidateSet("Full", "Diff", "Log")][Alias('Type')]
        [String]$BackupType = 'Log',
        [Parameter(HelpMessage="Enter DateTime in 24 hours format (yyyy-MM-dd hh:mm:ss)")]
        [String]$StopAtTime
    )

    # StopAt in String format
    if ([string]::IsNullOrEmpty($StopAtTime) -eq $false) {
        try {
            Write-Verbose "`$StopAtTime = '$StopAtTime'";

            $format = "yyyy-MM-dd HH:mm:ss";
            Write-Verbose "`$format = '$format'";

            $StopAt_Time = [DateTime]::ParseExact($StopAtTime, $format, $null);
            Write-Verbose "`$StopAt_Time = '$StopAt_Time'";

            $StopAt_String = ($StopAt_Time).ToString('MMM dd, yyyy hh:mm:ss tt');
            Write-Verbose "`$StopAt_String = '$StopAt_String'";
        }
        catch {
            Write-Error "Invalid datetime format specified for `$StopAt_Time parameter. Kindly use format: (yyyy-MM-dd hh:mm:ss)";
            return;
        }
    }

    # Format $Database input
    if([String]::IsNullOrEmpty($Database) -eq $false) {
        #$Database = @('StackOverflow2010','DBA_Snapshot','Staging,Staging2,StagingFiltered','[Mosaic],[MosaicFiltered],RCM_morecore_20130710_NoMusic1a_en-US')

        Write-Verbose "Formatting `$Database parameter";
        # Create array of databases removing single quotes, square brackets, other other wrong formats
        $DatabaseList = @();
        foreach($dbItem in $Database) {
            $arrayItems = $dbItem.Split(',');
            foreach($arrItem in $arrayItems) {
                $arrItem = $arrItem.Replace("'",''); # remove single quotes
                $arrItem = ($arrItem.Replace('[','')).Replace(']',''); # remove square brackets
                $DatabaseList += $arrItem;
            }
        }

        $Database = $DatabaseList;
        $DatabaseCommaList = "'$($Database -join "','")'";
    }

    # Format $ExcludeDatabase input
    if([String]::IsNullOrEmpty($ExcludeDatabase) -eq $false) {
        # Create array of databases removing single quotes, square brackets, other other wrong formats

        Write-Verbose "Formatting `$ExcludeDatabase parameter";
        $ExcludeDatabaseList = @();
        foreach($dbItem in $ExcludeDatabase) {
            $arrayItems = $dbItem.Split(',');
            foreach($arrItem in $arrayItems) {
                $arrItem = $arrItem.Replace("'",''); # remove single quotes
                $arrItem = ($arrItem.Replace('[','')).Replace(']',''); # remove square brackets
                $ExcludeDatabaseList += $arrItem;
            }
        }

        $ExcludeDatabase = $ExcludeDatabaseList;
        $ExcludeDatabaseCommaList = "'$($ExcludeDatabase -join "','")'";
    }

    if($Database.Count -gt 0 -and $ExcludeDatabase -gt 0) {
        Write-Host "Parameters `$Database and $ExcludeDatabase are not compatible. Kindly one one of them at a time.";
        return;
    }

    # Final Query to execute against Destination
    [System.Collections.ArrayList]$fileHeaders = @();

    Write-Verbose "Creating Tsql query to find database backup history";

    # Query to find database backup history
    $query_databasesFromBackupHistory = @"
SET NOCOUNT ON;
DECLARE @dbName VARCHAR(125),
        @backupStartDate datetime,
        @stopAtTime datetime;
DECLARE @DiffLSN [numeric](25, 0);
DECLARE @SQLString_Full nvarchar(2000);
DECLARE @SQLString_Diff nvarchar(2000);
DECLARE @SQLString_Log nvarchar(2000);
DECLARE @ParmDefinition nvarchar(500);
 
IF OBJECT_ID('tempdb..#BackupHistory') IS NOT NULL
    DROP TABLE #BackupHistory;
CREATE TABLE #BackupHistory
(
    [BackupFile] [nvarchar](260) NULL,
    [BackupTypeDescription] [varchar](21) NULL,
    [ServerName] [char](100) NULL,
    [UserName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [DatabaseCreationDate] [datetime] NULL,
    [BackupSize] [numeric](20, 0) NULL,
    [FirstLSN] [numeric](25, 0) NULL,
    [LastLSN] [numeric](25, 0) NULL,
    [CheckpointLSN] [numeric](25, 0) NULL,
    [DatabaseBackupLSN] [numeric](25, 0) NULL,
    [BackupStartDate] [datetime] NULL,
    [BackupFinishDate] [datetime] NULL,
    [CompatibilityLevel] [tinyint] NULL,
    [Collation] [nvarchar](128) NULL,
    [IsCopyOnly] [bit] NULL,
    [RecoveryModel] [nvarchar](60) NULL
) ;
 
/* Build the SQL string to get latest full backup for database. */
SET @SQLString_Full =
     N'SELECT BackupFile = bmf.physical_device_name,
            CASE bs.type WHEN ''D'' THEN ''Database'' WHEN ''I'' THEN ''Differential database'' WHEN ''L'' THEN ''Log'' ELSE NULL END as BackupTypeDescription,
            LTRIM(RTRIM(CAST(SERVERPROPERTY(''ServerName'') AS VARCHAR(125)))) as ServerName,
            UserName = bs.user_name,
            bs.database_name,
            DatabaseCreationDate = bs.database_creation_date,
            BackupSize = COALESCE(bs.compressed_backup_size,bs.backup_size),
            FirstLSN = bs.first_lsn,
            LastLSN = bs.last_lsn,
            CheckpointLSN = bs.checkpoint_lsn,
            DatabaseBackupLSN = bs.database_backup_lsn,
            BackupStartDate = bs.backup_start_date,
            BackupFinishDate = bs.backup_finish_date,
            CompatibilityLevel = bs.compatibility_level,
            Collation = bs.collation_name,
            IsCopyOnly = bs.is_copy_only,
            RecoveryModel = bs.recovery_model
    FROM msdb.dbo.backupmediafamily AS bmf
    INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
    WHERE bs.type = ''D'' AND is_copy_only = 0 AND database_name = @q_dbName
    AND bs.backup_start_date >= @q_backupStartDate';
 
"@
;

    if($BackupType -in ('Diff','Log')) {
        $query_databasesFromBackupHistory += @"
 
/* Build the SQL string to get latest Differential backup for database. */
SET @SQLString_Diff =
     N'SELECT BackupFile = bmf.physical_device_name,
            CASE bs.type WHEN ''D'' THEN ''Database'' WHEN ''I'' THEN ''Differential database'' WHEN ''L'' THEN ''Log'' ELSE NULL END as BackupTypeDescription,
            LTRIM(RTRIM(CAST(SERVERPROPERTY(''ServerName'') AS VARCHAR(125)))) as ServerName,
            UserName = bs.user_name,
            bs.database_name,
            DatabaseCreationDate = bs.database_creation_date,
            BackupSize = COALESCE(bs.compressed_backup_size,bs.backup_size),
            FirstLSN = bs.first_lsn,
            LastLSN = bs.last_lsn,
            CheckpointLSN = bs.checkpoint_lsn,
            DatabaseBackupLSN = bs.database_backup_lsn,
            BackupStartDate = bs.backup_start_date,
            BackupFinishDate = bs.backup_finish_date,
            CompatibilityLevel = bs.compatibility_level,
            Collation = bs.collation_name,
            IsCopyOnly = bs.is_copy_only,
            RecoveryModel = bs.recovery_model
    FROM msdb.dbo.backupmediafamily AS bmf
    INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
    WHERE bs.type = ''I'' AND is_copy_only = 0 AND database_name = @q_dbName
    AND bs.backup_start_date >= (SELECT MAX(bs.backup_start_date) AS Latest_DiffBackupDate
                                    FROM msdb.dbo.backupmediafamily AS bmf INNER JOIN msdb.dbo.backupset AS bs
                                    ON bmf.media_set_id = bs.media_set_id
                                    WHERE bs.type=''I'' and is_copy_only = 0
                                    AND database_name = @q_dbName
                                    AND bs.backup_start_date >= @q_backupStartDate
                                    )';
 
"@
;
    }

    if($BackupType -eq 'Log') {
        $query_databasesFromBackupHistory += @"
 
/* Build the SQL string to get all latest Log backups after differential */
SET @SQLString_Log =
     N'SELECT BackupFile = bmf.physical_device_name,
            CASE bs.type WHEN ''D'' THEN ''Database'' WHEN ''I'' THEN ''Differential database'' WHEN ''L'' THEN ''Log'' ELSE NULL END as BackupTypeDescription,
            LTRIM(RTRIM(CAST(SERVERPROPERTY(''ServerName'') AS VARCHAR(125)))) as ServerName,
            UserName = bs.user_name,
            bs.database_name,
            DatabaseCreationDate = bs.database_creation_date,
            BackupSize = COALESCE(bs.compressed_backup_size,bs.backup_size),
            FirstLSN = bs.first_lsn,
            LastLSN = bs.last_lsn,
            CheckpointLSN = bs.checkpoint_lsn,
            DatabaseBackupLSN = bs.database_backup_lsn,
            BackupStartDate = bs.backup_start_date,
            BackupFinishDate = bs.backup_finish_date,
            CompatibilityLevel = bs.compatibility_level,
            Collation = bs.collation_name,
            IsCopyOnly = bs.is_copy_only,
            RecoveryModel = bs.recovery_model
    FROM msdb.dbo.backupmediafamily AS bmf
    INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
    WHERE bs.type = ''L'' AND is_copy_only = 0 AND database_name = @q_dbName
    AND bs.backup_start_date >= @q_backupStartDate
    AND ( @q_DiffLSN < bs.first_lsn
                OR
                (@q_DiffLSN >= bs.first_lsn and @q_DiffLSN <= bs.last_lsn)
            )';
 
"@
;
    }

    $query_databasesFromBackupHistory += @"
   
DECLARE databases_cursor CURSOR LOCAL FORWARD_ONLY FOR
        -- Find latest Full backup for each database
        SELECT MAX(bs.backup_start_date) AS Latest_FullBackupDate, database_name
        FROM msdb.dbo.backupmediafamily AS bmf INNER JOIN msdb.dbo.backupset AS bs
        ON bmf.media_set_id = bs.media_set_id WHERE bs.type='D' and is_copy_only = 0
        $( if([String]::IsNullOrEmpty($StopAt_String) -eq $false){ "AND bs.backup_start_date <= '$StopAtTime'"} )
        $( if($ExcludeDatabase.Count -gt 0){ "AND database_name NOT IN ($ExcludeDatabaseCommaList)" } )
        $( if($Database.Count -gt 0){ "AND database_name IN ($DatabaseCommaList)" } )
        GROUP BY database_name;
 
OPEN databases_cursor
FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DiffLSN = NULL;
    BEGIN TRY
        SET @ParmDefinition = N'@q_dbName varchar(125), @q_backupStartDate datetime2';
        -- Find latest full
        INSERT #BackupHistory
        EXECUTE sp_executesql @SQLString_Full, @ParmDefinition,
                              @q_dbName = @dbName,
                              @q_backupStartDate = @backupStartDate;
     
"@
;

    if($BackupType -in ('Diff','Log')) {
        $query_databasesFromBackupHistory += @"
 
        -- Find latest differential
        INSERT #BackupHistory
        EXECUTE sp_executesql @SQLString_Diff, @ParmDefinition,
                              @q_dbName = @dbName,
                              @q_backupStartDate = @backupStartDate;
 
"@
;
    }

    if($BackupType -eq 'Log') {
        $query_databasesFromBackupHistory += @"
 
        SELECT @DiffLSN = LastLSN FROM #BackupHistory WHERE DatabaseName = @dbName AND BackupTypeDescription = 'Differential database';
        IF @DiffLSN IS NULL
            SELECT @DiffLSN = LastLSN FROM #BackupHistory WHERE DatabaseName = @dbName AND BackupTypeDescription = 'Database';
 
        SET @ParmDefinition = N'@q_dbName varchar(125), @q_backupStartDate datetime2, @q_DiffLSN [numeric](25, 0)';
        -- Find latest log
        INSERT #BackupHistory
        EXECUTE sp_executesql @SQLString_Log, @ParmDefinition,
                              @q_dbName = @dbName,
                              @q_backupStartDate = @backupStartDate,
                              @q_DiffLSN = @DiffLSN ;
 
"@
;
    }

    $query_databasesFromBackupHistory += @"
 
    END TRY
    BEGIN CATCH
        PRINT ' -- ---------------------------------------------------------';
        PRINT ERROR_MESSAGE();
        PRINT ' -- ---------------------------------------------------------';
    END CATCH
         
    FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName;
END
 
CLOSE databases_cursor;
DEALLOCATE databases_cursor ;
 
SELECT * FROM #BackupHistory
ORDER BY DatabaseName, BackupTypeDescription, BackupStartDate;
"@
;

    $srvObj = Connect-DbaInstance -SqlInstance $SqlInstance;
    
    Write-Verbose "Querying [$SqlInstance] for backup history";
    $tBackupHistory = Invoke-DbaQuery -SqlInstance $srvObj -Query $query_databasesFromBackupHistory;
    #$nodes = @(Invoke-DbaQuery -SqlInstance $srvObj -Query 'SELECT NodeName FROM sys.dm_os_cluster_nodes;' | Select-Object -ExpandProperty NodeName);
        
    $ServerName = $SqlInstance.Split('\')[0];
    $ssn = New-PSSession -ComputerName $ServerName -Name $ServerName;

    Write-Verbose "Looping through backup file from BackupHistory to find existence";
    foreach ($backup in $tBackupHistory) { 
           
        $bkpFile = $backup.BackupFile;
        $BackupFile_NetworkPath = "\\$ServerName\"+($bkpFile.Replace(':','$'));

        $FilePresentOnDisk = Invoke-Command -Session $ssn -ScriptBlock {[System.IO.File]::Exists($Using:bkpFile)}
        $BackupSizeBytes = $backup.BackupSize;
        $BackupSize = [PSCustomObject]@{
                            Bytes = $BackupSizeBytes;
                            KiloBytes = [math]::round($BackupSizeBytes / 1Kb,2);
                            MegaByte = [math]::round($BackupSizeBytes / 1Mb,2);
                            GigaByte = [math]::round($BackupSizeBytes / 1Gb,2);
                            TeraByte = [math]::round($BackupSizeBytes / 1Tb,2);
                        }
        
        $MethodBlock = {    if($this.TeraByte -ge 1) {
                                "$($this.TeraByte) tb"
                            }elseif ($this.GigaByte -ge 1) {
                                "$($this.GigaByte) gb"
                            }elseif ($this.MegaByte -ge 1) {
                                "$($this.MegaByte) mb"
                            }elseif ($this.KiloBytes -ge 1) {
                                "$($this.KiloBytes) kb"
                            }else {
                                "$($this.Bytes) bytes"
                            }
                        }
        $BackupSize | Add-Member -MemberType ScriptMethod -Name tostring -Value $MethodBlock -Force;

        $headerInfo = [PSCustomObject]@{
                                ServerName = $(($backup.ServerName).Trim());
                                DatabaseName = $backup.DatabaseName;                                                            
                                BackupTypeDescription = $backup.BackupTypeDescription;                                
                                BackupSize = $BackupSize;
                                BackupFile = $bkpFile;
                                BackupStartDate = $backup.BackupStartDate;
                                BackupFinishDate = $backup.BackupFinishDate;
                                RecoveryModel = $backup.RecoveryModel;
                                FilePresentOnDisk = $FilePresentOnDisk;
                                BackupFile_NetworkPath = $BackupFile_NetworkPath;  
                                FirstLSN = $backup.FirstLSN;
                                LastLSN = $backup.LastLSN;
                                CheckpointLSN = $backup.CheckpointLSN;
                                DatabaseBackupLSN = $backup.DatabaseBackupLSN;
                                CompatibilityLevel = $backup.CompatibilityLevel;
                                Collation = $backup.Collation;
                    }
        $fileHeaders.Add($headerInfo) | Out-Null;
    } # Loop through $tBackupHistory

    $ssn | Remove-PSSession;
    Write-Output $fileHeaders
}