Verify TFS Backup Status with PowerShell

A few years ago I posted about how to verify the TFS backup job status by executing a query on the SQL DB. Now the same can achieved remotely with the help of PowerShell:

function Get-TfsBackup {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline)]
        [string]$ComputerName
    )
    
    begin {
       $backupquery = "
       SELECT command,
s.text,
start_time,
percent_complete,
estimated_completion_time/1000 as [seconds to go],
dateadd(second,estimated_completion_time/1000, getdate()) as [estimated completion time]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')  
       " 
    }
    
    process {
        Write-Verbose -Message "Checking if backups are running"
            $result =  Invoke-Sqlcmd -Query $backupquery -ServerInstance $ComputerName
      if ($result.Count -eq 0)
      {
        "No backups currently running!"
       }
       else
       {
         $result
       }
    }
    
    end {
    }
}