SQL server 2005 backup statistic

Question

I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.

Answer 1

One of the slickest ways I've seen to control backups is Ola Hallengren's free maintenance script solution:

http://ola.hallengren.com/

It's installed as a series of stored procs in Master that do all kinds of nifty utility tasks. It'll automatically handle newly added databases, leverages third party backup compression tools, and does index rebuilds/defrags too.

Answer 2

This query draws from msdb backup history and shows volumes and numbers aggregated weekly. It should be straightforward to modify it to fit your specific needs.

;with backups as (
 select 
      bs.*
    , backupTarget = case 
          when bs.type = 'D' then 'FULL'
          when bs.type = 'I' then 'DIFF'
          when bs.type = 'L' then 'LOG'
          else 'OTHER'
          end
    , groupDate = dateadd(week, DATEDIFF(WEEK, '2000-01-01', bs.backup_start_date), '2000-01-01')
 from msdb.dbo.backupset bs
)
select 
 groupDate
 , groupWeek  = cast(year(groupDate) + DATEPART(week, groupDate) / 100.0 as decimal(8, 2))
 , [Full MB]  = cast(sum( case backupTarget when 'FULL' then backup_size else 0 end ) / 1024 / 1024 as int)
 , [Diff MB]  = cast(sum( case backupTarget when 'DIFF' then backup_size else 0 end ) / 1024 / 1024 as int)
 , [Log MB]   = cast(sum( case backupTarget when 'LOG' then backup_size else 0 end ) / 1024 / 1024 as int)
 , [Other MB] = cast(sum( case backupTarget when 'OTHER' then backup_size else 0 end ) / 1024 / 1024 as int)            
 , [Full #]   = sum( case backupTarget when 'FULL' then 1 else 0 end )
 , [Diff #]   = sum( case backupTarget when 'DIFF' then 1 else 0 end ) 
 , [Log #]    = sum( case backupTarget when 'LOG' then 1 else 0 end ) 
 , [Other #]  = sum( case backupTarget when 'OTHER' then 1 else 0 end )  
 , [Full s]   = sum( case backupTarget when 'FULL' then DATEDIFF(s, backup_start_date, backup_finish_date) else 0 end ) 
 , [Diff s]   = sum( case backupTarget when 'DIFF' then DATEDIFF(s, backup_start_date, backup_finish_date) else 0 end ) 
 , [Log s]    = sum( case backupTarget when 'LOG' then DATEDIFF(s, backup_start_date, backup_finish_date) else 0 end ) 
 , [Other s]  = sum( case backupTarget when 'OTHER' then DATEDIFF(s, backup_start_date, backup_finish_date) else 0 end )
 from backups 
group by 
    groupDate
order by 1 desc