Functions/Get-SdtDbaRestoreScript.ps1

function Get-SdtDbaRestoreScript {
<#
.SYNOPSIS
This function return tsql RESTORE DATABASE/LOG code for performing restore operation
.DESCRIPTION
This function accepts source and target servers, and generates copy and database restore scripts
.PARAMETER SourceSqlInstance
Name of source SqlInstance
.PARAMETER TargetSqlInstance
Name of destination server where backups are to be restored
.PARAMETER Database
Names of databases for scripting RESTORE code
.PARAMETER ExcludeDatabase
Names of databases that are to be skipped
.PARAMETER EstimateBackupSize
With this switch, result will have latest full and diff backup size along with duration from source
.PARAMETER EstimateRestoreSpaceRequirement
With this switch, result will have database data and log file space requirement
.PARAMETER OnlyCopyCommand
With this switch, result will generate only robocopy commands
.PARAMETER OnlyRestoreCommand
With this switch, the result will generate only RESTORE commands
.PARAMETER BackupCopySizeThreshold_GB
Size threshold of Backup files after which it should be called Large backup. Default is 25 GB.
.PARAMETER Type
Choose the depth of backup restore. Available options are Full, Diff and Log. Default is Log
.PARAMETER RecentOnly
With this switch, script will choose latest backup between history & copied target file.
.PARAMETER OverwriteExistingFileDirectories
With this switch, script will choose data/log file location on target according to MappingExcel ignoring the existing path on Target if db is already present.
.PARAMETER MappingExcel
Full path for Excel file having at least 3 sheets namely 'RestoreAs', 'TargetCopiedFileLocation' and 'DbDriveMapping'
.PARAMETER PointInTime
With this parameter, the RESTORE scripts generated will perform Point-In-Time recovery of databases
.PARAMETER Recover
With this switch, the Restore script generated would bring the databases online
.PARAMETER GenerateSampleMappingExcel
With this switch, the result will generate a sample 'MappingExcel' file
.EXAMPLE
Get-SdtDbaRestoreScript -SourceSqlInstance testvm01 -TargetSqlInstance testvm02
This command generates robocopy and restore scripts for all user databases from testvm01 to testvm02
.EXAMPLE
Get-SdtDbaRestoreScript -SourceSqlInstance testvm01 -TargetSqlInstance testvm02 -Database @('Staging','[Mosaic],RCM_morecore_20130710_NoMusic1a_en-US','DBA, ADI, Amazon','Babel') -MappingExcel C:\Temp\MappingExcel.xlsx
This command reads sheets 'RestoreAs', 'TargetCopiedFileLocation' and 'DbDriveMapping' from MappingExcel, and generates copy & restore script for copying databases from testvm01 to testvm02
.EXAMPLE
Get-SdtDbaRestoreScript -SourceSqlInstance testvm01 -EstimateRestoreSpaceRequirement
This command returns collection of databases with its common attributes and information of sizes for total/full/diff backups along with data & log file size.
.EXAMPLE
$Databases = @('Staging','[Mosaic],RCM_morecore_20130710_NoMusic1a_en-US','DBA, ADI, Amazon','Babel');
Get-SdtDbaRestoreScript -SourceSqlInstance testvm01 -Database $Databases -GenerateSampleMappingExcel -MappingExcel C:\Temp\MappingExcel.xlsx
 
This command generates MappingExcel.xlsx for databases with sheets 'RestoreAs', 'TargetCopiedFileLocation' and 'DbDriveMapping' on C:\Temp\. If the excel already exists, the sheets data would be overwritten.
.LINK
https://github.com/imajaydwivedi/SQLDBATools
.LINK
https://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/
.LINK
https://youtu.be/v4r2lhIFii4
.NOTES
Author: Ajay Dwivedi
EMail: ajay.dwivedi2007@gmail.com
Date: Oct 25, 2019
Documentation: https://github.com/imajaydwivedi/SQLDBATools
#>

    [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='None')]
    Param(
        [Parameter(Mandatory=$true,Position=0)][ValidateNotNullOrEmpty()]
        [String]$SourceSqlInstance,
        [String]$TargetSqlInstance,
        [String[]]$Database,
        [String[]]$ExcludeDatabase,
        [Switch]$EstimateBackupSize, #done
        [Switch]$EstimateRestoreSpaceRequirement, #done
        [Switch]$OnlyCopyCommand,
        [Int]$BackupCopySizeThreshold_GB = 25,
        [Switch]$ExecuteCopyCommand,
        [Switch]$OnlyRestoreCommand,
        [Switch]$ExecuteRestoreCommand,
        [ValidateSet("Full", "Diff", "Log")][Alias('RestoreType')]
        [String]$Type = 'Log',
        [Switch]$OverwriteExistingFileDirectories,
        [String]$MappingExcel,
        [Parameter(HelpMessage="Enter DateTime in 24 hours format (yyyy-MM-dd hh:mm:ss)")]
        [String]$PointInTime,
        [Switch]$Recover,
        [Switch]$GenerateSampleMappingExcel #done
    )
    BEGIN {
        Write-Verbose "Inside BEGIN block"
        $srcObj = Connect-DbaInstance -SqlInstance $SourceSqlInstance;
        if(-not [String]::IsNullOrEmpty($TargetSqlInstance)) {
            $tgtObj = Connect-DbaInstance -SqlInstance $TargetSqlInstance;
        }

        $SourceServerName = $SourceSqlInstance.Split('\')[0];
        $SourceServerNodeName = Invoke-Command -ComputerName $SourceServerName -ScriptBlock {$env:COMPUTERNAME};
        
        $TargetServerName = $TargetSqlInstance.Split('\')[0];
        if(-not [string]::IsNullOrEmpty($TargetServerName)) {
            $TargetServerNodeName = Invoke-Command -ComputerName $TargetServerName -ScriptBlock {$env:COMPUTERNAME};
        }

        Write-Verbose "Validate Parameter Sets";
        if($GenerateSampleMappingExcel -and ([String]::IsNullOrEmpty($PointInTime) -eq $false -or $RecentOnly -or $OnlyRestoreCommand -or $OnlyCopyCommand -or $EstimateRestoreSpaceRequirement -or $EstimateBackupSize) ) {
            Write-Error "Parameter 'GenerateSampleMappingExcel' is not valid with below parameters:-`n
PointInTime, RecentOnly, OnlyRestoreCommand, OnlyCopyCommand, EstimateRestoreSpaceRequirement, EstimateBackupSize"
;
            return;
        }        
    }
    PROCESS {
        Write-Verbose "Get-SdtDbaRestoreScript called";        
        
        # Validate $StopAtTime
        if ([string]::IsNullOrEmpty($StopAtTime) -eq $false) {
            # StopAt in String format
            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;
            }
        } # Validate $StopAtTime

        # Get Databases using TSQL
        if($true) {
            Write-Verbose "Inside block: Get Databases using TSQL";
            # Format $Database input
            if([String]::IsNullOrEmpty($Database) -eq $false) {
                #$Database = @('StackOverflow2010','DBA_Snapshot','Staging,Staging2,StagingFiltered','[Mosaic],[MosaicFiltered],RCM_morecore_20130710_NoMusic1a_en-US')

                # 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
                        $arrItem = $arrItem.Trim(); # remove Space
                        $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
                        $arrItem = $arrItem.Trim(); # remove Space
                        $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;
            }

            # TSQL - Get Database MetaData From Source SqlInstance
            $srcDatabases_tsql = @"
select d.name, d.compatibility_level, d.state_desc, d.recovery_model_desc
from sys.databases as d
where d.database_id > 4
$(if($Database.Count -gt 0){" and d.name in ($DatabaseCommaList)"})
$(if($ExcludeDatabase.Count -gt 0){"where d.name not in ($ExcludeDatabaseCommaList)"});
"@
;
            Write-Verbose "Find Databases to Query";
            #$rSrcDatabases = @();
            $rSrcDatabases = Invoke-DbaQuery -SqlInstance $srcObj -Database 'master' -Query $srcDatabases_tsql -Verbose:$false;
            $rTgtDatabases = Invoke-DbaQuery -SqlInstance $tgtObj -Database 'master' -Query $srcDatabases_tsql -Verbose:$false;

            #Write-Debug "Before Throwing Error";
            $srcDatabasesNamesOnly = @($rSrcDatabases.name);            
            if( ($Database.Count -ne $srcDatabasesNamesOnly.Count) -and (-not [string]::IsNullOrEmpty($Database)) ) {
                $missingDatabases = (Compare-Object -ReferenceObject $Database -DifferenceObject ($rSrcDatabases | Select -ExpandProperty name)).InputObject;
            
                Write-Host "Below databases are not present on Source:`n$($missingDatabases -join "`n")`n" -ForegroundColor Red;
                return;
            }
        } # Get Databases using TSQL

        # Get-SdtBackupHistory
        if($true) {
            #Write-Debug "Before Get-SdtBackupHistory";
            Write-Verbose "Find backup history for databases using Get-SdtBackupHistory";
            $BackupsAllDbs = Get-SdtBackupHistory -SqlInstance $SourceSqlInstance -Database $($rSrcDatabases | Select -ExpandProperty name) -BackupType $(if($EstimateBackupSize){'Diff'}else{$Type}) -StopAtTime $PointInTime;
        } # Get-SdtBackupHistory

        # $EstimateBackupSize
        if($EstimateBackupSize) {
            Write-Verbose "Estimate Backup Size";
            Write-Output $($BackupsAllDbs | Where-Object {$_.BackupTypeDescription -in @('Database','Differential database')});
            return;
        } # $EstimateBackupSize

        # Create Database Object
        if($true){
            Write-Verbose "Create database object with all properties"
            [System.Collections.ArrayList]$srcDatabaseObjects = @();
            
            $dbFilesTarget_tsql = @"
select db_name(mf.database_id) as DbName, mf.file_id, mf.type_desc, mf.name, mf.physical_name
from sys.master_files mf
where mf.database_id > 4
$(if($Database.Count -gt 0){" and DB_NAME(mf.database_id) in ($DatabaseCommaList)"})
$(if($ExcludeDatabase.Count -gt 0){"and DB_NAME(mf.database_id) not in ($ExcludeDatabaseCommaList)"});
"@
;
            $rDbFiles_Tgt = Invoke-DbaQuery -SqlInstance $tgtObj -Database $rDb.name -Query $dbFilesTarget_tsql -ErrorAction SilentlyContinue;
            
            # TSQL - Get Database Files for Source SqlInstance
            $dbFiles_tsql = @"
select df.type_desc, df.name, df.physical_name, df.state_desc, df.size, df.max_size, df.growth
from sys.database_files as df
"@
;
                        
            # Loop through each Database
            foreach($rDb in $rSrcDatabases) {
                Write-Verbose " process [$($rDb.name)] database.."

                # TSQL - Get database files
                $rDbFiles = Invoke-DbaQuery -SqlInstance $srcObj -Database $rDb.name -Query $dbFiles_tsql;

                # Filter Backup Files for database
                if(-not [String]::IsNullOrEmpty($BackupsAllDbs)) {
                    $rDbBackups = $BackupsAllDbs | Where-Object {$PSItem.DatabaseName -eq $rDb.name} | Sort-Object -Property BackupTypeDescription, BackupStartDate;
                    $MethodBlock = {
                        if($this.BackupTypeDescription -in @('Database','Differential database')) {
                            $this.BackupFile | Split-Path -Leaf;
                        }
                    }
                    $rDbBackups | Add-Member -MemberType ScriptMethod -Name tostring -Value $MethodBlock -Force;
                } # Filter Backup Files for database
                
                # Create Size object
                if($true){
                    $DataFilesSizePages = ($rDbFiles | Where-Object {$_.type_desc -eq 'ROWS'} | Measure-Object -Property size -Sum).Sum;
                    $DataFileSize = [PSCustomObject]@{
                                        Pages = $DataFilesSizePages;
                                        Bytes = [math]::Round($DataFilesSizePages * 8.0 * 1024,2);
                                        KiloByte = [math]::Round($DataFilesSizePages * 8.0,2);
                                        MegaByte = [math]::Round(($DataFilesSizePages * 8.0) / 1024,2);
                                        GigaByte = [math]::Round(($DataFilesSizePages * 8.0) / 1024 / 1024,2);
                                        TeraByte = [math]::Round(($DataFilesSizePages * 8.0) / 1024 / 1024 / 1024,2);
                                    }
            
                    $LogFilesSizePages = ($rDbFiles | Where-Object {$_.type_desc -eq 'LOG'} | Measure-Object -Property size -Sum).Sum;
                    $LogFileSize = [PSCustomObject]@{
                                        Pages = $LogFilesSizePages;
                                        Bytes = [math]::Round($LogFilesSizePages * 8.0 * 1024,2);
                                        KiloByte = [math]::Round($LogFilesSizePages * 8.0,2);
                                        MegaByte = [math]::Round(($LogFilesSizePages * 8.0) / 1024,2);
                                        GigaByte = [math]::Round(($LogFilesSizePages * 8.0) / 1024 / 1024,2);
                                        TeraByte = [math]::Round(($LogFilesSizePages * 8.0) / 1024 / 1024 / 1024,2);
                                    }

                    $SizePages = $DataFilesSizePages + $LogFilesSizePages;
                    $Size = [PSCustomObject]@{
                                    Pages = $SizePages;
                                    Bytes = [math]::Round($SizePages * 8.0 * 1024,2);
                                    KiloByte = [math]::Round($SizePages * 8.0,2);
                                    MegaByte = [math]::Round(($SizePages * 8.0) / 1024,2);
                                    GigaByte = [math]::Round(($SizePages * 8.0) / 1024 / 1024,2);
                                    TeraByte = [math]::Round(($SizePages * 8.0) / 1024 / 1024 / 1024,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"
                                    }
                                }
                    $DataFileSize | Add-Member -MemberType ScriptMethod -Name tostring -Value $MethodBlock -Force;
                    $LogFileSize | Add-Member -MemberType ScriptMethod -Name tostring -Value $MethodBlock -Force;
                    $Size | Add-Member -MemberType ScriptMethod -Name tostring -Value $MethodBlock -Force;
                } # Create Size object

                # Create psobject
                $obj = [PSCustomObject]@{
                            DbName  = $rDb.name;
                            Size = $Size;
                            DataFileSize = $DataFileSize;
                            LogFileSize = $LogFileSize;
                            Files = $rDbFiles;
                            Compatibility_level = $rDb.compatibility_level;
                            State_Desc = $rDb.state_desc;
                            Recovery_Model_Desc = $rDb.recovery_model_desc;
                            Backups = $(if(-not [String]::IsNullOrEmpty($rDbBackups)) {$rDbBackups}else{"Not Applicable"});
                        } # Create psobject

                $srcDatabaseObjects.Add($obj) | Out-Null;
            } # Loop through each Database

            
            Write-Verbose " Add FullBackupSize & DiffBackupSize properties to database object";
            $srcDatabaseObjects | ForEach-Object -Begin {} -Process {
                $FullBackupSize = ($_.Backups | Where-Object {$_.BackupTypeDescription -eq 'Database'}).BackupSize;
                $DiffBackupSize = ($_.Backups | Where-Object {$_.BackupTypeDescription -eq 'Differential database'}).BackupSize;
                $_ | Add-Member -NotePropertyName FullBackupSize -NotePropertyValue $FullBackupSize;
                $_ | Add-Member -NotePropertyName DiffBackupSize -NotePropertyValue $DiffBackupSize;
            } -End {};
            
            $srcDatabases = ($srcDatabaseObjects | Select-Object DbName, Size, DataFileSize, LogFileSize, FullBackupSize, DiffBackupSize, Compatibility_level, State_Desc, Recovery_Model_Desc, Files, Backups);
        } # Create Database Object

        # Logic for GenerateSampleMappingExcel
        if($GenerateSampleMappingExcel) {
            #Write-Debug "Inside GenerateSampleMappingExcel";

            if([String]::IsNullOrEmpty($MappingExcel)) {
                Write-Verbose "Generate Sample Mapping Excel";
                $MappingExcel = [System.IO.Path]::ChangeExtension([System.IO.Path]::GetTempFileName(), 'xlsx');
            }

            $Sheet_RestoreAs = @();
            $Sheet_RestoreAs_Prop01 = [Ordered]@{DbName = 'DummySRC';RestoreAs = 'DummyTGT'}
            $Sheet_RestoreAs_Obj01 = New-Object -TypeName psobject -Property $Sheet_RestoreAs_Prop01;
            $Sheet_RestoreAs_Prop02 = [Ordered]@{DbName = 'TestSRC';RestoreAs = 'TestTGT'}
            $Sheet_RestoreAs_Obj02 = New-Object -TypeName psobject -Property $Sheet_RestoreAs_Prop02;
            $Sheet_RestoreAs += $Sheet_RestoreAs_Obj01;
            $Sheet_RestoreAs += $Sheet_RestoreAs_Obj02;
            $Sheet_RestoreAs | Export-Excel -Path $MappingExcel -WorksheetName 'RestoreAs' -ClearSheet;

            # Initialize blank Arrays
            [System.Collections.ArrayList]$Sheet_TargetCopiedFileLocation = @();
            [System.Collections.ArrayList]$Sheet_DbDriveMapping = @();

            foreach($db in $srcDatabases) {
                if($db.FullBackupSize.GigaByte -gt $BackupCopySizeThreshold_GB) {
                    $Sheet_TargetCopiedFileLocation_Prop = [PSCustomObject]@{DbName = $db.DbName;BackupPath = 'G:\MSSQLData\DbRefreshActivity\'}
                    $Sheet_TargetCopiedFileLocation.Add($Sheet_TargetCopiedFileLocation_Prop) | Out-Null;
                }

                $Sheet_DbDriveMapping_Prop = [PSCustomObject]@{DbName = $db.DbName;DataDrive = 'S:\'; LogDrive = 'S:\'}
                $Sheet_DbDriveMapping.Add($Sheet_DbDriveMapping_Prop) | Out-Null;
            }

            $Sheet_TargetCopiedFileLocation | Export-Excel -Path $MappingExcel -WorksheetName 'TargetCopiedFileLocation' -ClearSheet;
            $Sheet_DbDriveMapping | Export-Excel -Path $MappingExcel -WorksheetName 'DbDriveMapping' -ClearSheet;

            Write-Host "Excel '$MappingExcel' is created";
            return;
        } # Logic for GenerateSampleMappingExcel

        if($EstimateRestoreSpaceRequirement) {
            Write-Verbose "Estimate Restore Space Requirement";
            Write-Output $srcDatabases;
            return;
        }

        # Generate Copy Commands for Full/Diff backups
        if($true) {
            #Write-Debug "Generate Copy Commands";
            Write-Verbose "Inside Generate Copy Commands for Full/Diff backups";
            
            if(-not [String]::IsNullOrEmpty($MappingExcel) -and $(Test-Path $MappingExcel)) {
                Write-Verbose "Import MappingExcel data";
                $Sheet_RestoreAs = Import-Excel -Path $MappingExcel -WorksheetName 'RestoreAs';
                $Sheet_TargetCopiedFileLocation = Import-Excel -Path $MappingExcel -WorksheetName 'TargetCopiedFileLocation';
                $Sheet_DbDriveMapping = Import-Excel -Path $MappingExcel -WorksheetName 'DbDriveMapping';
            }

            # Join BackupFiles with Sheet_TargetCopiedFileLocation
            if($true) {
                Write-Verbose "Join BackupFiles with Sheet_TargetCopiedFileLocation"
                $TargetCopyFileLocation_Default = 'C:';
                #if($TargetCopyFileLocation_Default.Length -le 3) {
                 # $TargetCopyFileLocation_Default = "$TargetCopyFileLocation_Default\";
                #}
                $UniqueTargetPaths = $Sheet_TargetCopiedFileLocation | Select-Object -Property @{l='BackupPath';e={($_.BackupPath).TrimEnd('\')}} -Unique;
                $BackupFiles = $srcDatabases.Backups | Where-Object {$_.BackupSize.GigaByte -gt $BackupCopySizeThreshold_GB} | Select-Object DatabaseName, BackupTypeDescription, BackupSize, @{l='FileName';e={Split-Path -Path $_.BackupFile -Leaf}}, @{l='FileDirectory';e={Split-Path -Path $_.BackupFile}};
                foreach($bkpFile in $BackupFiles) {
                    $TargetCopyFileLocation = ($Sheet_TargetCopiedFileLocation | Where-Object {$_.DbName -eq $bkpFile.DatabaseName}).BackupPath
                    if(-not [string]::IsNullOrEmpty($TargetCopyFileLocation)) {
                        $bkpFile | Add-Member -NotePropertyName TargetCopyFileLocation -NotePropertyValue $($TargetCopyFileLocation.TrimEnd('\'));
                    }else {
                        $bkpFile | Add-Member -NotePropertyName TargetCopyFileLocation -NotePropertyValue $TargetCopyFileLocation_Default;
                    }
                }                
            } # Join BackupFiles with Sheet_TargetCopiedFileLocation

            # Generate CopyCommand Collection
            if($true) {
                $UniqueBackupDirectories = $BackupFiles | Select-Object -Property @{l='FileDirectory';e={($_.FileDirectory).TrimEnd('\')}} -Unique;
                [System.Collections.ArrayList]$CopyCommands = @();
                $UniqueBackupDirectories_FileDirectories = $UniqueBackupDirectories.FileDirectory;
                $UniqueTargetPaths_BackupPath = $UniqueTargetPaths.BackupPath;
                if([string]::IsNullOrEmpty($UniqueTargetPaths_BackupPath)) {
                    $UniqueTargetPaths_BackupPath = $TargetCopyFileLocation_Default;
                }
                foreach($cpPath in $UniqueBackupDirectories_FileDirectories) {
                    foreach($pstPath in $UniqueTargetPaths_BackupPath) {
                        # find backup files present on $cpPath to be copied on $pstPath
                        $files2Copy = ($BackupFiles | Where-Object {$_.FileDirectory -eq $cpPath -and $_.TargetCopyFileLocation -eq $pstPath} | Select-Object @{l='FileName';e={"`"$($_.FileName)`""}}).FileName -join ' ';
                        #$pstNetworkPath = "\\$TargetServerName\"+$pstPath.Replace(':','$');

                        if(-not [String]::IsNullOrEmpty($files2Copy)) {
                            $CopyCommand = "robocopy \\$SourceServerNodeName\$($cpPath.Replace(':','$')) $(if($pstPath.Length -lt 3){"$pstPath\"}else{$pstPath}) /it $files2Copy";
                            $TargetPathDrive = "$(Split-Path -Path $pstPath -Qualifier)\";
                            $CopyCommandObj = [PSCustomObject]@{
                                                    SourcePath = $cpPath;
                                                    TargetPath = $pstPath;
                                                    TargetPathDrive = $TargetPathDrive;
                                                    CopyCommand = $CopyCommand;
                                                }
                            $CopyCommands.Add($CopyCommandObj) | Out-Null;
                        }
                    } # Loop through each Target Directory
                } # Loop Through each Backup Directory
            } # Generate CopyCommand Collection
        } # Generate Copy Commands for Full/Diff backups

        # Return $CopyCommands
        if($OnlyCopyCommand) {
            Write-Verbose "Return `$CopyCommands";
            Write-Output $CopyCommands;
            #Write-Debug "Before exit";
            return;
        } # Return $CopyCommands

        # Generate Restore Commands
        if($true) {
            Write-Verbose "Inside Generate Copy Commands for Full/Diff backups";
            if(-not [String]::IsNullOrEmpty($MappingExcel) -and $(Test-Path $MappingExcel)) {
                if([String]::IsNullOrEmpty($Sheet_DbDriveMapping)) {
                    Write-Verbose "Import MappingExcel data";
                    $Sheet_RestoreAs = Import-Excel -Path $MappingExcel -WorksheetName 'RestoreAs';
                    $Sheet_TargetCopiedFileLocation = Import-Excel -Path $MappingExcel -WorksheetName 'TargetCopiedFileLocation';
                    $Sheet_DbDriveMapping = Import-Excel -Path $MappingExcel -WorksheetName 'DbDriveMapping';
                }
            }

            $LargeBackupDbNames = ($BackupsAllDbs | Where-Object {$_.BackupTypeDescription -eq 'Database' -and $_.BackupSize.GigaByte -gt $BackupCopySizeThreshold_GB}).DatabaseName;
            $SmallBackupDbNames = ($BackupsAllDbs | Where-Object {$_.BackupTypeDescription -eq 'Database' -and $_.BackupSize.GigaByte -le $BackupCopySizeThreshold_GB}).DatabaseName;
            
            # Find database Files on Destination
            if($true){
                Write-Verbose "Find database Files on Destination";                
                $rSrcDatabases_CommaList = (($rSrcDatabases | Select-Object @{l='name';e={"'$($_.name)'"}}).name -join ',');

                # TSQL - Get Database Files for Source SqlInstance
                $dbFiles_tsql = @"
select DB_NAME(df.database_id) as DatabaseName, df.type_desc, df.name, df.physical_name, df.state_desc, df.size, df.max_size, df.growth
from sys.master_files as df
where DB_NAME(df.database_id) in ($rSrcDatabases_CommaList);
"@
;
                $rTgtDatabaseFiles = Invoke-DbaQuery -SqlInstance $tgtObj -Database master -Query $dbFiles_tsql;
            }
            
            Write-Verbose "Creating loop counter variables..";
            # Creating loop counter variables..
            if($true) {
                $fileCounter_Database = 1;
                $fileCounterTotal_Database = 1;
                $dbCounter = 1;
                $dbCounter_Total = $rSrcDatabases.Count;
                [System.Collections.ArrayList]$RestoreCommands = @();
                $tsqlRestoreDatabaseFiles = '';
            } # Creating loop counter variables..

            # Loop through each backup file to add Restore Script object
            foreach($bkpFile in $BackupsAllDbs) {
                #Write-Debug "Generate Restore Commands";
                Write-Verbose "Processing backup file '$($bkpFile.BackupFile)'";
                if([String]::IsNullOrEmpty($dbName)) {
                    $dbName = $bkpFile.DatabaseName;
                    $tsqlRestoreDatabaseFiles = '';
                }

                # reset counters for next database
                if($dbName -ne $bkpFile.DatabaseName) {
                    #Write-Debug "Creating RestoreCommand Object"
                    $NewDataFilePath = $finalDbFiles | Where-Object {$_.type_desc -eq 'ROWS'} | Select-Object -ExpandProperty physical_name_new -First 1;
                    $NewDataFileDirectory = Split-Path -Path $NewDataFilePath;
                    $NewDataFileDrive = "$(Split-Path -Path $NewDataFilePath -Qualifier)\";
                    $restoreObj = [PSCustomObject]@{
                                        DbName = $dbName;
                                        DestinationDirectory = $NewDataFileDirectory;
                                        DestinationDrive = $NewDataFileDrive;
                                        RestoreCommand = $tsqlRestoreDatabaseFiles;
                                    }
                    $RestoreCommands.Add($restoreObj) | Out-Null;

                    $tsqlRestoreDatabaseFiles = '';
                    $dbCounter += 1;
                    $fileCounter_Database = 1;
                    $dbName = $bkpFile.DatabaseName;
                } # reset counters for next database

                $isCopiedLocally = $false;
                $SetFileRecovery = $false;

                # Is backup file copied locally on target
                if($bkpFile.BackupSize.GigaByte -gt $BackupCopySizeThreshold_GB -and $bkpFile.BackupTypeDescription -in @('Database','Differential database')) {
                    Write-Verbose "Verifying if backup file is copied locally on target"
                    $isCopiedLocally = $true;
                    $MappingBackupPath = ($Sheet_TargetCopiedFileLocation | Where-Object {$_.DbName -eq $dbName}).BackupPath;
                    if(-not [string]::IsNullOrEmpty($MappingBackupPath)) {
                        $MappingBackupPath = $(if($MappingBackupPath.EndsWith('\')){$MappingBackupPath}else{"$MappingBackupPath\"});
                    } else {
                        $MappingBackupPath = $(if($TargetCopyFileLocation_Default.EndsWith('\')){$TargetCopyFileLocation_Default}else{"$TargetCopyFileLocation_Default\"});
                    }
                    $backupFilePath = $MappingBackupPath+$(Split-Path -Path $bkpFile.BackupFile -Leaf);
                } # Is backup file copied locally on target
                
                # Is file to be restore from network
                if(-not $isCopiedLocally) {
                    $backupFilePath = $bkpFile.BackupFile_NetworkPath;
                } # Is file to be restore from network
                
                # Set recovery flag if $Recover is used, and there is no other backup to process
                if($Recover) {
                    $RemainingDbBackupsToProcess = $BackupsAllDbs | Where-Object {$_.DatabaseName -eq $dbName -and $_.BackupStartDate -gt $bkpFile.BackupStartDate};
                    if([string]::IsNullOrEmpty($RemainingDbBackupsToProcess)) {
                        $SetFileRecovery = $true;
                    }
                }

                if($bkpFile.BackupTypeDescription -eq 'Database') {
                    Write-Verbose "creating variable for Full backup case";

                    $finalDbFiles = @();
                    $tgtDbFiles = $rTgtDatabaseFiles | Where-Object {$_.DatabaseName -eq $dbName};
                    $tgtDbFilesArray = $tgtDbFiles.name;
                    $srcDbFiles = ($SrcDatabases | Where-Object {$_.DbName -eq $dbName}).Files;
                    $srcDbFilesArray = $srcDbFiles.name;
                    $filesMapping = $Sheet_DbDriveMapping | Where-Object {$_.DbName -eq $dbName};

                    
                    $totalBackupFiles4Database = $BackupsAllDbs | Where-Object {$_.DatabaseName -eq $dbName};
                    if([string]::IsNullOrEmpty($totalBackupFiles4Database.Count)) { $fileCounterTotal_Database = 1 }
                    else {$fileCounterTotal_Database = $totalBackupFiles4Database.Count;}

                    
                    # If db file mapping is provided, then put source files according to mapping
                    if(-not [string]::IsNullOrEmpty($filesMapping)) {
                        Write-Verbose "`tMake variable adjustment according to MappingExcel";

                        $finalDbFiles = $srcDbFiles;
                        $filesMapping_DataString = $filesMapping.DataDrive;
                        $filesMapping_LogString = $filesMapping.LogDrive;
                        
                        # If log location is not mentioned, then use Data location for it
                        if([string]::IsNullOrEmpty($filesMapping_LogString)){$filesMapping_LogString = $filesMapping_DataString}

                        # If only drive letter mentioned
                        if($filesMapping_DataString.Length -le 3) {
                            $filesMapping_DataDrive = if($filesMapping_DataString.EndsWith('\')){$filesMapping_DataString}else{"$filesMapping_DataString\"}
                        }else {
                            $filesMapping_DataDirectory = if($filesMapping_DataString.EndsWith('\')){$filesMapping_DataString}else{"$filesMapping_DataString\"}
                        }
                        if($filesMapping_LogString.Length -le 3) {
                            $filesMapping_LogDrive = if($filesMapping_LogString.EndsWith('\')){$filesMapping_LogString}else{"$filesMapping_LogString\"}
                        }else {
                            $filesMapping_LogDirectory = if($filesMapping_LogString.EndsWith('\')){$filesMapping_LogString}else{"$filesMapping_LogString\"}
                        }

                        Write-Verbose "`t`tAdd new physical_name attribute to `$finalDbFiles"
                        # add new physical_name attribute to $finalDbFiles
                        foreach($file in $finalDbFiles) {
                            $physical_name = $file.physical_name;
                            $physical_name_basefile = (Split-Path -Path $physical_name -Leaf);
                            $physical_name_directory = (Split-Path -Path $physical_name);

                            # get new physical_name
                            if($file.type_desc -eq 'ROWS') {
                                if(-not [string]::IsNullOrEmpty($filesMapping_DataDrive)) {
                                    $physical_name_new = $physical_name -replace "^[A-Za-z]:\\*", $filesMapping_DataDrive;
                                } else {
                                    $physical_name_new = "$filesMapping_DataDirectory\$physical_name_basefile";
                                }
                            }else {
                                if(-not [string]::IsNullOrEmpty($filesMapping_LogDrive)) {
                                    $physical_name_new = $physical_name -replace "^[A-Za-z]:\\*", $filesMapping_LogDrive;
                                } else {
                                    $physical_name_new = "$filesMapping_LogDirectory\$physical_name_basefile";
                                }
                            } # get new physical_name

                            # Add new attribute
                            $file | Add-Member -NotePropertyName physical_name_new -NotePropertyValue $physical_name_new;
                        } # add new physical_name attribute to $finalDbFiles
                    } # If db file mapping is provided, then put source files according to mapping


                    # If db file mapping is not provided, then use path of destination until $OverwriteExistingFileDirectories swith is used
                    if([string]::IsNullOrEmpty($filesMapping)) {
                        Write-Verbose "`tMake variable adjustment when MappingExcel value not found";

                        # Use Target file path if $OverwriteExistingFileDirectories
                        if($OverwriteExistingFileDirectories) {
                            $finalDbFiles = $srcDbFiles;
                        }else {
                            if(-not [string]::IsNullOrEmpty($tgtDbFiles)) {
                                $CmprResult = Compare-Object -ReferenceObject $srcDbFiles -DifferenceObject $tgtDbFiles -IncludeEqual;
                                if($CmprResult.SideIndicator -contains '=>' -or $CmprResult.SideIndicator -contains '<=') {
                                    Write-Host "Database files on '$SourceSqlInstance' and '$TargetSqlInstance' differ for database [$dbName].`nKindly use `$OverwriteExistingFileDirectories switch to overwrite." -ForegroundColor Red;
                                    return
                                }                                
                                $finalDbFiles = $tgtDbFiles;
                            }else {
                                $finalDbFiles = $srcDbFiles;
                            }
                        } # Use Target file path if $OverwriteExistingFileDirectories

                        Write-Verbose "`t`tAdd new physical_name attribute to `$finalDbFiles"
                        # add new physical_name attribute to $finalDbFiles
                        foreach($file in $finalDbFiles) {
                            $physical_name_new = $file.physical_name;
                            # Add new attribute
                            $file | Add-Member -NotePropertyName physical_name_new -NotePropertyValue $physical_name_new;
                        } # add new physical_name attribute to $srcDbFiles
                        
                    } # If db file mapping is not provided, then use path of destination until $OverwriteExistingFileDirectories swith is used
                }
                
                #Write-Debug "`$finalDbFiles collection is created";

                $tsqlSeparater = @"
`n-- =========================================================================
-- =========================================================================
"@
;
                
                $tsqlPrintStmt_Database = "`nPRINT '$dbCounter) Restoring database [$dbName]';";
                
                $tsqlSetOffline = @"
`n-- Set database to Offline
USE master;
ALTER DATABASE [$dbName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
"@
;
                        
                $tsqlPrintStmt_File = "`n`tPRINT ' File no: $fileCounter_Database of $fileCounterTotal_Database';";
                                    
                $tsqlRestore_Full = @"
`nRESTORE DATABASE [$dbName] FROM DISK = '$backupFilePath'
  WITH $(if($SetFileRecovery){"RECOVERY"}else{"NORECOVERY"}), $(if($OverwriteExistingFileDirectories){"REPLACE, "}else{''})STATS = 5
"@
;
                
                $tsqlCreateFolder = "`n-- Create Data/Log folder";
                foreach($file in $finalDbFiles) {
                    $tsqlRestore_Full += "`n`t,MOVE '$($file.name)' TO '$($file.physical_name_new)'";
                    $tsqlCreateFolder += "`nEXEC master.sys.xp_create_subdir '$(Split-Path -Path $file.physical_name_new)';";
                }
                $tsqlCreateFolder += "`nGO`n"
                
                $tsqlRestore_NonFull = @"
`nRESTORE $(if($bkpFile.BackupTypeDescription -eq 'Log'){"LOG"}else{"DATABASE"}) [$dbName] FROM DISK = '$backupFilePath'
  WITH $(if($SetFileRecovery){"RECOVERY"}else{"NORECOVERY"}), STATS = 5;
$(if($bkpFile.BackupTypeDescription -ne 'Log'){"GO"})
"@
;

                $tsqlRestore_Full += "`nGO";

                if($bkpFile.BackupTypeDescription -eq 'Database') {
                    $tsqlRestoreFinal = $tsqlCreateFolder + $tsqlPrintStmt_Database + $tsqlPrintStmt_File + $tsqlRestore_Full;
                    if($OverwriteExistingFileDirectories){$tsqlRestoreFinal = $tsqlSetOffline + $tsqlRestoreFinal}
                } else {
                    $tsqlRestoreFinal = $tsqlPrintStmt_File + $tsqlRestore_NonFull;
                }

                if($fileCounter_Database -eq $fileCounterTotal_Database) {
                    $tsqlRestoreFinal += $tsqlSeparater;
                }

                #Write-Verbose "`$tsqlRestoreFinal => ";
                #Write-Host $tsqlRestoreFinal -ForegroundColor Green;
                $tsqlRestoreDatabaseFiles += $tsqlRestoreFinal;

                #Write-Debug "End of Iteration $fileCounter_Database for [$dbName]"

                $fileCounter_Database += 1;
            } # Loop through each backup file to add Restore Script object

            Write-Verbose "Adding last RestoreCommand object in Collection";
            $NewDataFilePath = $finalDbFiles | Where-Object {$_.type_desc -eq 'ROWS'} | Select-Object -ExpandProperty physical_name_new -First 1;
            $NewDataFileDirectory = Split-Path -Path $NewDataFilePath;
            $NewDataFileDrive = "$(Split-Path -Path $NewDataFilePath -Qualifier)\";
            $restoreObj = [PSCustomObject]@{
                                DbName = $dbName;
                                DestinationDirectory = $NewDataFileDirectory;
                                DestinationDrive = $NewDataFileDrive;
                                RestoreCommand = $tsqlRestoreDatabaseFiles;
                            }
            $RestoreCommands.Add($restoreObj) | Out-Null;
        } # Generate Restore Commands

        # Return $OnlyRestoreCommand
        if($OnlyRestoreCommand) {
            Write-Verbose "Return RestoreCommands";
            Write-Output $RestoreCommands;
            return;
        }

        # Output Restore & Copy Commands
        if($true) {
            # Create File for storing copy commands
            $ResultFile_CopyCommands = "C:\temp\DbaRestoreScripts_CopyCommands_$(Get-Date -Format ddMMMyyyyTHHmm).txt";
            # Create File for storing restore commands of Large Dbs
            $ResultFile_RestoreLargeDbs = "C:\temp\DbaRestoreScripts_LargeDatabases_$(Get-Date -Format ddMMMyyyyTHHmm).sql";
            # Create File for storing restore commands of Small Dbs
            $ResultFile_RestoreSmallDbs = "C:\temp\DbaRestoreScripts_SmallDatabases_$(Get-Date -Format ddMMMyyyyTHHmm).sql";

            # Append Copy Commands to Notepad file order by TargetDrive
            if($true) {
                $SessionCounter = 1;
                # Loop through unique Target Path
                $DistinctTargetPathDrives = ($CopyCommands | Sort-Object -Property TargetPathDrive -Unique | Select-Object TargetPathDrive).TargetPathDrive;
                foreach($pth in $DistinctTargetPathDrives) {
                    $CopyCommandHeader = @"
`n<# ########## PSSession# $SessionCounter -- Copy Backup files on $pth of server $TargetServerName ####
    # Execute below Commands in Elevated Powershell ISE Session against Target server
 
"@
;
                    $CopyCommandsFiltered = ($CopyCommands | Where-Object {$_.TargetPathDrive -eq $pth} | Sort-Object -Property TargetPath);
                    foreach($cmd in $CopyCommandsFiltered) {
                        $CopyCommandHeader += "$($cmd.CopyCommand)`n";
                    }
                    $CopyCommandHeader += "`n`n";
                    $CopyCommandHeader | Out-File -Append $ResultFile_CopyCommands;
                    $SessionCounter += 1;
                }

                Write-Host "Opening generated copy script file '$ResultFile_CopyCommands' with notepad.";
                if (Test-Path $ResultFile_CopyCommands) {
                    notepad.exe $ResultFile_CopyCommands;
                }else {
                    Write-Host "No file found to be copied locally on Target server..";
                }
            } # Append Copy Commands to Notepad file order by TargetDrive

            # Append Restore Commands of Large Databases to Notepad file order by TargetDrive
            if($true) {
                #Write-Debug "Generate Restore commands in Text File";
                $SessionCounter = 1;
                # Loop through unique Target Path
                $DistinctTargetPathDrives = ($RestoreCommands | Where-Object {$_.DbName -in $LargeBackupDbNames} | Sort-Object -Property DestinationDrive -Unique | Select-Object DestinationDrive).DestinationDrive;
                foreach($pth in $DistinctTargetPathDrives) {
                    $RestoreCommandHeader = @"
`n-- ########## Session# $SessionCounter -- Restore database on $pth of instance [$TargetSqlInstance] ####
    -- Execute below TSQL statements in a SSMS against Target server
 
"@
;
                    $RestoreCommandsFiltered = ($RestoreCommands | Where-Object {$_.DbName -in $LargeBackupDbNames -and $_.DestinationDrive -eq $pth} | Sort-Object -Property DestinationDirectory);
                    foreach($cmd in $RestoreCommandsFiltered) {
                        $RestoreCommandHeader += "$($cmd.RestoreCommand)`n";
                    }
                    $RestoreCommandHeader += "`n`n";
                    $RestoreCommandHeader | Out-File -Append $ResultFile_RestoreLargeDbs;
                    $SessionCounter += 1;
                }

                Write-Host "Opening generated restore script file '$ResultFile_RestoreLargeDbs' with notepad.";
                if (Test-Path $ResultFile_RestoreLargeDbs) {
                    notepad.exe $ResultFile_RestoreLargeDbs;
                }else {
                    Write-Host "No backup found to be restored on Target server..";
                }
            } # Append Restore Commands of Large Databases to Notepad file order by TargetDrive

            # Append Restore Commands of Small Databases to Notepad file order by TargetDrive
            if($true) {
                # Filter for Small databases
                $RestoreCommandsFiltered = ($RestoreCommands | Where-Object {$_.DbName -in $SmallBackupDbNames} | Sort-Object -Property DbName);
                foreach($cmd in $RestoreCommandsFiltered) {
                    $RestoreCommandHeader = "$($cmd.RestoreCommand)`n";
                    $RestoreCommandHeader | Out-File -Append $ResultFile_RestoreSmallDbs;
                }
                
                Write-Host "Opening generated restore script file '$ResultFile_RestoreSmallDbs' with notepad.";
                if (Test-Path $ResultFile_RestoreSmallDbs) {
                    notepad.exe $ResultFile_RestoreSmallDbs;
                }else {
                    Write-Host "No small backup found to be restored on Target server..";
                }
            } # Append Restore Commands of Large Databases to Notepad file order by TargetDrive


        } # Output Restore & Copy Commands
        return

        # Output Source Databases
        Write-Output $srcDatabases;
        
    }
    END {
    }
}