backup size

• ramyours2003

  SSChampion

  Points: 12268

  More actions

  February 10, 2011 at 7:51 am

  #149892

  As i have 150 databases in a server i want to calculate the growth of all database sizes and to find the backup size of every db , can any one help me in this .

  instead of not creating a db in he system as it is production one .

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  February 10, 2011 at 8:07 am

  #1285228

  To find backup size for Every DB

  select database_name,backup_size,backup_start_date from msdb.dbo.backupset

  order by database_name

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  February 10, 2011 at 8:18 am

  #1285240

  Something like this ? This assumes you don't clear your full backup history from msdb. This shows the average backup size per month. You can extrapolate actual database size.

  select substring(a.database_name,1,35) as 'Database',

  datepart(year,a.backup_start_date) as 'year',

  datepart(month,a.backup_start_date) as 'month' ,

  avg(cast((a.backup_size /1073741824) as decimal (9,2)))as 'Avg Gig'

  FROM msdb.dbo.backupset a

  join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name

  WHERE a.type = 'D' and b.type = 'D'

  --and a.backup_size > 1073741824 -- > 1 Gig

  and a.backup_start_date >= '2009-12-01'

  GROUP BY a.database_name,datepart(year,a.backup_start_date),datepart(month,a.backup_start_date)

  order by a.database_name,datepart(year,a.backup_start_date) desc,datepart(month,a.backup_start_date) desc