Script to obtain most recent SQL Server backup information for all databases

$labServer = "XXX"
$inventoryDB = "XXX"

#Clean the BackupStatus table
#This is where the information of all the backups from all the instances will be stored
#You can omit the TRUNCATE statement if you would like to keep all the information every single time you collect it from each instance.
Invoke-Sqlcmd -Query "TRUNCATE TABLE XXX" -Database $inventoryDB -ServerInstance $labServer

#Fetch all the instances with the respective SQL Server Version
$instanceLookupQuery = /* Put in here the query that will return the list of instances under your support, ideally you already have a Master Server List to work with */

$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery

$backupsQuery = "
WITH MostRecentBackups
   AS(
      SELECT 
         database_name AS [Database],
         MAX(bus.backup_finish_date) AS LastBackupTime,
         CASE bus.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Differential'
            WHEN 'L' THEN 'Transaction Log'
         END AS Type
      FROM msdb.dbo.backupset bus
      WHERE bus.type <> 'F'
      GROUP BY bus.database_name,bus.type
   ),
   BackupsWithSize
   AS(
      SELECT mrb.*, (SELECT TOP 1 CONVERT(DECIMAL(10,4), b.backup_size/1024/1024/1024) AS backup_size FROM msdb.dbo.backupset b WHERE [Database] = b.database_name AND LastBackupTime = b.backup_finish_date) AS [Backup Size]
      FROM MostRecentBackups mrb
   )
   
   SELECT 
      SERVERPROPERTY('ServerName') AS Instance, 
      d.name AS [Database],
      d.state_desc AS State,
      d.recovery_model_desc AS [Recovery Model],
      bf.LastBackupTime AS [Last Full],
      DATEDIFF(DAY,bf.LastBackupTime,GETDATE()) AS [Time Since Last Full (in Days)],
      bf.[Backup Size] AS [Full Backup Size],
      bd.LastBackupTime AS [Last Differential],
      DATEDIFF(DAY,bd.LastBackupTime,GETDATE()) AS [Time Since Last Differential (in Days)],
      bd.[Backup Size] AS [Differential Backup Size],
      bt.LastBackupTime AS [Last Transaction Log],
      DATEDIFF(MINUTE,bt.LastBackupTime,GETDATE()) AS [Time Since Last Transaction Log (in Minutes)],
      bt.[Backup Size] AS [Transaction Log Backup Size]
   FROM sys.databases d
   LEFT JOIN BackupsWithSize bf ON (d.name = bf.[Database] AND (bf.Type = 'Full' OR bf.Type IS NULL))
   LEFT JOIN BackupsWithSize bd ON (d.name = bd.[Database] AND (bd.Type = 'Differential' OR bd.Type IS NULL))
   LEFT JOIN BackupsWithSize bt ON (d.name = bt.[Database] AND (bt.Type = 'Transaction Log' OR bt.Type IS NULL))
   WHERE d.name <> 'tempdb' AND d.source_database_id IS NULL
"

#For each instance, grab the backups information for all the databases
foreach ($instance in $instances){
   #Go grab the backups information for all the databases in the instance
   Write-Host "Fetching Backups information for databases on instance" $instance.instance
   $results = Invoke-Sqlcmd -Query $backupQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

   #Perform the INSERT in the BackupStatus table only if it returned at least 1 row
    if($results.Length -ne 0){

      #Build the insert statement
      $insert = "INSERT INTO XXX VALUES"
      foreach($result in $results){
            if($result['Last Full'].ToString().trim() -eq [String]::Empty){$LastFull = "''"} else{$LastFull = $result['Last Full'] }
            if($result['Time Since Last Full (in Days)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastFull = "''"} else{$TimeSinceLastFull = $result['Time Since Last Full (in Days)']}
            if($result['Full Backup Size'].ToString().trim() -eq [String]::Empty){$FullBackupSize = "''"} else{$FullBackupSize = $result['Full Backup Size']}
            if($result['Last Differential'].ToString().trim() -eq [String]::Empty){$LastDifferential = "''"} else{$LastDifferential = $result['Last Differential'] }
            if($result['Time Since Last Differential (in Days)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastDifferential = "''"} else{$TimeSinceLastDifferential = $result['Time Since Last Differential (in Days)']}
            if($result['Differential Backup Size'].ToString().trim() -eq [String]::Empty){$DifferentialBackupSize = "''"} else{$DifferentialBackupSize = $result['Differential Backup Size']}
            if($result['Last Transaction Log'].ToString().trim() -eq [String]::Empty){$LastTransactionLog = "''"} else{$LastTransactionLog = $result['Last Transaction Log'] }
            if($result['Time Since Last Transaction Log (in Minutes)'].ToString().trim() -eq [String]::Empty){$TimeSinceLastTransactionLog = "''"} else{$TimeSinceLastTransactionLog = $result['Time Since Last Transaction Log (in Minutes)']}
            if($result['Transaction Log Backup Size'].ToString().trim() -eq [String]::Empty){$TransactionLogBackupSize = "''"} else{$TransactionLogBackupSize = $result['Transaction Log Backup Size']}

         $insert += "
         (
         '"+$result['Instance']+"',
         '"+$result['Database']+"',
         '"+$result['State']+"',
         '"+$result['Recovery Model']+"',
         '"+$LastFull+"',
            "+$TimeSinceLastFull+",
         "+$FullBackupSize+",
            '"+$LastDifferential+"',
            "+$TimeSinceLastDifferential+",
         "+$DifferentialBackupSize+",
            '"+$LastTransactionLog+"',
            "+$TimeSinceLastTransactionLog+",
         "+$TransactionLogBackupSize+",
            GETDATE()
         ),
         "
            $insert = $insert -replace "''",'NULL'
            $insert = $insert -replace "NULLNULL",'NULL'
      }

      #Store the results in the table from your central database where you will have the information from all the instances.
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
   }				}

Write-Host "Done!"