Job for Backup audit

  • Hi,

    I am trying to create a job for backup audits in a production server. Can someone give me the guidelines? That would be great!

    Thanks

  • -- Run this procedure to create a nice report for backups

    -- Backups are flagged current if within 24 hours old

    -- this also tracks deleted databases (marked Legacy) just in case you need to restore

    Create Procedure BackupListing

    as

    SET NOCOUNT OFF

    SET ANSI_WARNINGS off

    declare@typevarchar(20)

    declare@DBInvarchar(100)

    declare@optionsvarchar(100)

    set @type = 'Listing'

    set@DBIn = 'All'

    set@options = ' WITH INIT '

    if @type = 'database'

    set @type = 'Full'

    select'ServerName' = v.srvname,

    isnull(d.name, (s.database_name + ' (Legacy)')) as 'DBName',

    max(s.backup_start_date) 'LastFullBackup',

    case

    when max(s.backup_start_date) > dateadd(day, -1, getdate())

    then 'Current'

    --when max(s.backup_start_date) > dateadd(Month, -1, getdate())

    --then 'Warning'

    when max(s.backup_start_date) is Null

    then 'No Backup!'

    else 'Failed'

    end as 'Status'

    into #BU_Listing

    From msdb.dbo.backupset s

    full outer join master.dbo.sysdatabases d

    on d.name = s.database_name

    ,master.dbo.sysservers v

    where v.srvid = 0

    and ( s.type = 'D' or s.type is null )

    and isnull(d.name, (s.database_name)) not in ('tempdb', 'SQLPerformance', 'distribution')

    group by v.srvname ,isnull(d.name, (s.database_name + ' (Legacy)')) --, dev.logical_device_name, dev.physical_device_name

    order by 2

    selectServerName,

    DBName,

    case l.Status

    when 'Failed' then case

    when DBName like '%Legacy%'then 'N/A'

    else 'Failed'

    end

    --when 'Warning' then case

    --when max(Diff.backup_start_date) > dateadd(day, -1, getdate())

    --then 'Monthly'

    --else Status

    else l.Status

    end as BkupStatus,

    case

    when max(Diff.backup_start_date) > dateadd(day, -1, getdate())

    then 'Current'

    when max(Diff.backup_start_date) is Null

    then 'No Log!'

    else l.Status

    end as 'LogStatus',

    l.LastFullBackup,

    s.backup_size as 'BackupSize',

    max(Diff.backup_start_date) as 'LastLogBackup',

    dev.physical_device_nameas 'BackupLocation' ,

    isnull( dev.logical_device_name,' ' )as 'BackupDeviceUsed' ,

    bd.nameas 'BackupDeviceName',

    bd.phynameas 'BackupDeviceLocation'

    into #BU_Listing2

    from #BU_Listing l

    left outer join msdb.dbo.backupset s

    on l.DBName = s.database_name

    andl.LastFullBackup = s.backup_start_date

    left outer join msdb.dbo.backupmediafamily dev

    ON s.media_set_id = dev.media_set_id

    left outer join master.dbo.sysdevices bd

    on 'bu_' + l.DBName = bd.name

    left outer join msdb.dbo.backupset Diff

    on l.DBName = Diff.database_name

    and l.LastFullBackup < Diff.backup_start_date

    and Diff.type = 'L'

    group byServerName, DBName, LastFullBackup, l.Status, s.backup_size,

    isnull( dev.logical_device_name,' ' ), dev.physical_device_name,

    bd.name, bd.phyname

    Order by l.DBName

    update #BU_Listing2

    set BkupStatus = 'N/A'

    where DBName like '%(legacy)%'

    update #BU_Listing2

    set LogStatus = 'N/A'

    where BkupStatus = 'N/A'

    delete from #BU_Listing2

    where (BackupLocation like '%.1' or BackupLocation like '%.2' )

    and DBName in ( select DBName

    from #BU_Listing2

    group by DBName

    having count(*) > 1)

    select getdate() as ReportDate,

    ServerName, DBName, BkupStatus, LogStatus,

    LastFullBackup, BackupSize, LastLogBackup,

    BackupLocation, BackupDeviceUsed

    from #BU_Listing2

    drop table #BU_Listing

    drop table #BU_Listing2

  • Thank you very much Eric,

  • Great script. I am going to add it to my bag of tricks! Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply