如何使用 msdb.dbo.backupset 和 dbatools 收集環境中所有實例的所有歷史備份大小資訊

我有太多伺服器要管理，無法在每台伺服器上手動執行此操作，因此我想以某種方式使用 dbatools 模組和以下查詢來自動執行此操作以收集備份集資訊。

 WITH BackupsSize AS(
 SELECT TOP 1000
        rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
   , [Year]  = DATEPART(year,[backup_start_date])
   , [Month] = DATEPART(month,[backup_start_date])
   , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
   , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM 
   msdb.dbo.backupset
WHERE 
   [database_name] = N'A1000370D1'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY 
   [database_name]
   , DATEPART(yyyy,[backup_start_date])
   , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month]) 
--SECTION 1 END

--SECTION 2 BEGIN
SELECT 
  b.Year,
  b.Month,
  b.[Backup Size GB],
  0 AS deltaNormal,
  b.[Compressed Backup Size GB],
  0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT 
  b.Year,
  b.Month,
  b.[Backup Size GB],
  b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
  b.[Compressed Backup Size GB],
  b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
  SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
  FROM BackupsSize bs
  WHERE bs.rn = b.rn - 1
) AS d

我有一個位於數據庫表中的伺服器列表，併計劃使用 invoke-dbaquery 將一個 $sqlinstance 變數與我的實例列表一起載入。

我不確定並且需要幫助的是如何使用上述 SQL 腳本一次性收集所有伺服器上所有數據庫的歷史備份數據。如何在遍歷每個數據庫的同時也遍歷我的實例列表？

Get-DbaDbBackupHistory您可以使用from擷取這一切dbatools，不需要循環（它在內部循環）。

Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
Select-Object SQLInstance,Database,Start,Totalsize,CompressedBackupSize |`
Sort-Object -Property SQLInstance,Database,Start;

您可能需要深入研究Totalsize和CompressedBackupSize屬性以獲取未格式化的 MB 或 GB 的原始數。

Get-DbaDbBackupHistory -SqlInstance $instancelist -Type Full -Since (Get-Date).AddMonths(-13) -ExcludeDatabase master,model,msdb |`
Select-Object SQLInstance,Database,Start,@{n='TotalSizeMB';e={$_.TotalSize.MegaByte}},@{n='CompressedBackupSizeMB';e={$_.CompressedBackupSize.MegaByte}}|`
Sort-Object -Property SQLInstance,Database,Start;

您應該能夠將此輸出寫入表Write-DbaDbTableData

引用自：https://dba.stackexchange.com/questions/278665