$Repo = '<Repo Server>'
$InformationPreference = "Continue"
$Instances = Invoke-DbaQuery -SqlInstance $Repo -Database DBStats -query "select Servername from servers.servers where is_sql = 1 and decomm = 0 "

$CollectionTime = get-date 

 <##############################
 ##############################
 Find amount of free space on drive where you want to test your backups
 Parse out the exact value removing the "GB" 
 Convert to MBs and leaving 5% free space to account for other backup processes hitting this drive 
 If the DB sizs is larger than the $LeaveSpace variable then only do a Restore with VerifyOnly 
 ##############################
 ##############################>

  ##find amount of free space on drive where you want to test your backups
  [string]$SpaceFree = Get-DbaDiskSpace -ComputerName $repo |?{$_.Name -eq 'S:\'} | select-object Free

  ##Parse out the exact value removing the "GB" 
   $Split = ($SpaceFree.Split(' '))[0]
   [decimal]$Space = ($Split.Substring($Split.Indexof('=')+1))

  $LeaveSpace  = ($space * 1024) - (($space * 1024) * .05)

Foreach($Instance in $Instances)
    {  
    
    Write-Information "Checking DBSizes on $($Instance.ServerName) to see if DB Sizes are smaller than $($LeaveSpace) as we only have a limited amount of drive space to execute Full Restores"

      $DBs = Get-DbaDatabase -SqlInstance "$($Instance.ServerName)" -ExcludeDatabase Master,MSDB,TempDB,Model | select-object name, sizemb 
          Foreach($DB in $DBs)
            {

                if( $DB.sizeMB -gt $LeaveSpace)
                    {
                        ##execute a verify only as we dont have enough storeage for these DBs
                        write-information "$($instance.ServerName)...$($DB.Name) verifyonly as sizeMB is $($DB.SizeMB) which is greater than $($LeaveSpace)  " 
        
                        $BackupTest = Test-DbaLastBackup -Destination $Repo -SqlInstance $($Instance.Servername) -Database $($DB.Name)  -VerifyOnly  -IgnoreLogBackup `
                             | Select-Object SourceServer, TestServer, Database, FileExists, RestoreResult, DBCCResult, RestoreStart, RestoreEnd, DBCCStart, DBCCEnd, BackupDates, BackupFiles,  @{label = "CollectionTime";expression={$($CollectionTime)}}

                        $BackupTest | Write-DbaDataTable -SqlInstance $Repo -Database DBStats -Schema Collector -Table LastBackup -autocreate
            
                    }else
                        { 
                        ##full blown restore and checkdb as these are smaller DBs
                        write-information "$($instance.ServerName)...$($DB.Name) full blown restore and checkdb as sizeMB is $($DB.SizeMB) which is less than $($LeaveSpace) " 
            
                        $BackupTest = Test-DbaLastBackup  -Destination $Repo -SqlInstance $($instance.ServerName) -Database $($DB.Name) -Prefix LastBackup_ -IgnoreLogBackup -DataDirectory 'S:\DailyBackups\LastBackupDestination' -LogDirectory 'S:\DailyBackups\LastBackupDestination' `
                            | Select-Object SourceServer, TestServer, Database, FileExists, RestoreResult, DBCCResult, RestoreStart, RestoreEnd, DBCCStart, DBCCEnd, BackupDates, BackupFiles,  @{label = "CollectionTime";expression={$($CollectionTime)}}
 
                        $BackupTest | Write-DbaDataTable -SqlInstance $Repo -Database DBStats -Schema Collector -Table LastBackup -autocreate

                        } ##IF / Else Block 
            }## DB block

    }  ## Instance Block 


Invoke-DbaQuery -SqlInstance $Repo -Database DBStats -Query "Execute Email_Alert.LastBackup"