0

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?

2

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.