SELECT BackupRange.Database_name ,First_Backup ,Last_Backup ,DATEDIFF(dd,First_Backup,Last_Backup) AS days_history ,firstbackup.backup_size/1024/1024 AS FirstBackupSizeMB ,lastbackup.backup_size/1024/1024 AS LastBackupSizeMB ,(lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024) as GrowthMB ,CASE WHEN DATEDIFF(dd,First_Backup,Last_Backup) > 0 THEN ((lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024)) / DATEDIFF(dd,First_Backup,Last_Backup) ELSE 0 END AS GrowthRate_MBday FROM (SELECT [database_name] ,MIN(backup_start_date) AS 'First_Backup' ,MAX(backup_start_date) AS 'Last_Backup' FROM msdb.dbo.backupset WHERE [type] = 'D' --AND [database_name] = N'mydatabase' GROUP BY [database_name]) BackupRange LEFT JOIN msdb.dbo.backupset firstbackup ON firstbackup.database_name = BackupRange.database_name AND firstbackup.backup_start_date = BackupRange.First_Backup LEFT JOIN msdb.dbo.backupset lastbackup ON lastbackup.database_name = BackupRange.database_name AND lastbackup.backup_start_date = BackupRange.last_Backup
Tuesday, 28 November 2017
Database growth from available backup history
Subscribe to:
Posts (Atom)