How to use msdb.dbo.backupset and dbatools to gather all of the historical backup size information for all instances in an environment

Question

I have too many servers to manage to want to do this manually on each server so I'd like to somehow automate this using the dbatools module and the below query for gathering the backupset information.

  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

I have a list of servers sitting in table on a db and plan to load a $sqlinstance variable with my list of instances using invoke-dbaquery.

What I'm not sure about and need help with is how to use the above SQL script to gather the historical backup data for all databases on all servers in one go. How can I loop through each database while also looping through my list of instances?

Answer 1

You can capture this all with Get-DbaDbBackupHistory from dbatools, no loops required (it loops internally).

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;

You may want to delve into the Totalsize and CompressedBackupSize properties to get the raw number of MB or GB without formatting.

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;

You should be able to write this output to a table with Write-DbaDbTableData

Answer 2

If you need to loop through each SQL instance and database one-by-one, this is the approach I would use:

foreach ($s in $sqlinstance){
     $dbs = Get-DbaDatabase -SqlInstance $s -ExcludeSystem
     foreach ($d in $dbs){
         Invoke-DbaQuery -Sqlinstance $s -Database $d -Query $querystring
    }

However, I prefer this approach better:

Get-DbaDatabase -SqlInstance $sqlinstance -ExcludeSystem | Invoke-DbaQuery -Query $querystring

For more information, see the examples 3 and 4 for Invoke-DbaQuery at the dbatools help site