Сборка данных о бэкапах старше Х дней

# получаем список серверов из заранее приготовленного файла
$servers = get-content servers.txt

foreach ($server in $servers) {
Write-Output "======"
Write-Output $server
# создаем соединение
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
# задаем строку соединения
$SqlConnection.ConnectionString = "Server=$($server);Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

# сам запрос
$SqlCmd.CommandText = "SELECT database_name = sd.name, backup_finish_date, type
FROM master.dbo.sysdatabases sd LEFT OUTER JOIN (
SELECT bs.database_name, backup_finish_date, type = case type WHEN ‘D’ THEN ‘Database’
WHEN ‘I’ THEN ‘Database Differential’
WHEN ‘L’ THEN ‘Log’
WHEN ‘F’ THEN ‘File or Filegroup’
end, backup_size
FROM msdb.dbo.backupset bs, (
SELECT database_name,max_backup_finish_date = max(backup_finish_date)
FROM msdb.dbo.backupset group by database_name) bs1
WHERE bs.database_name = bs1.database_name
AND bs.backup_finish_date = bs1.max_backup_finish_date ) bs3
ON bs3.database_name = sd.name
WHERE sd.name NOT IN (‘tempdb’)
AND (backup_finish_date < getdate() -7 or backup_finish_date IS NULL
)
ORDER BY sd.name ASC, backup_finish_date DESC"

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()
$DataSet.Tables[0]
}