Tuesday, January 21, 2014

SQL Server: Script to get backup size

Script to get total and average backup size on the SQL Server.


  ;With CTE_BKPSize AS
  (SELECT @@SERVERNAME AS ServerName,CAST(backup_finish_date AS DATE) AS BackupDate, SUM(CAST(CAST(compressed_backup_size AS FLOAT)/1024/1024 AS DECIMAL(11,2))) AS CompressedSizeMB
  FROM [msdb].[dbo].[backupset]
  GROUP BY CAST(backup_finish_date AS DATE)
  )
  SELECT ServerName, COUNT(*) AS NoOfDays, SUM(CompressedSizeMB) AS TotalBKPSize, CAST(AVG(CompressedSizeMB)  AS DECIMAL(13,2)) AS AvgBKPSize FROM CTE_BKPSize

  GROUP BY ServerName

1 comment: