SQL Server Performance

Backup History Analysis

Discussion in 'Contribute Your SQL Server Scripts' started by homebrew01, Aug 24, 2005.

  1. homebrew01 New Member

    I use these scripts every now & then to look at all my database backup's history at a glance and make sure everything's happening when it's supposed to. I have an optional date filter in the where clause.


    --+++++++++++++++++++++++ DISPLAY BACKUP INFORMATION

    -- Display ALL backup info w/ elapsed time ... ORDER BY DATABASE, DATE

    SELECT a.server_name as 'Server',
    a.database_name as 'Database',
    convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
    convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
    DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
    datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
    cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
    as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
    case
    when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
    then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
    else 0
    end as 'Meg/Min',
    ceiling(a.backup_size /1048576) as 'Size Meg' ,--cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,
    cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
    a.user_name,a.backup_size as 'Raw Size'
    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_start_date > '2005-01-01'
    group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
    order by a.server_name, a.database_name, a.backup_start_date desc


    +++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Display ALL backup info w/ elapsed time ... ORDER BY DATE, DATABASE

    SELECT a.server_name as 'Server',
    a.database_name as 'Database',
    convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
    convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
    DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
    datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
    cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
    as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
    case
    when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
    then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
    else 0
    end as 'Meg/Min',
    ceiling(a.backup_size /1048576) as 'Size Meg' ,--cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,
    cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig',-- div by 1073741824 to get gig
    a.user_name,a.backup_size as 'Raw Size'
    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_start_date > '2004-09-01'
    group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
    order by a.backup_start_date desc, a.server_name, a.database_name

Share This Page