Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Thank you Brent, I'll look into this –  Robert Ševčík - Robajz Aug 12 '11 at 9:25

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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