Sql-Server
如何使用 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