Home Forums SQL Server 2005 Backups Redgate Differential Backup and Restore RE: Redgate Differential Backup and Restore

  • Run something like this to see your backup history and see if there are full backups running at other times. You can add DIFF and LOG to get more results:

    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' /*D=Full*/ AND a.backup_start_date > getdate() -40 -- Last X days

    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