February 10, 2010 at 10:52 am
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
February 11, 2010 at 3:26 pm
-- 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 @type varchar(20)
declare @DBIn varchar(100)
declare @options varchar(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
select ServerName,
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_name as 'BackupLocation' ,
isnull( dev.logical_device_name,' ' ) as 'BackupDeviceUsed' ,
bd.name as 'BackupDeviceName',
bd.phyname as 'BackupDeviceLocation'
into #BU_Listing2
from #BU_Listing l
left outer join msdb.dbo.backupset s
on l.DBName = s.database_name
and l.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 by ServerName, 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
February 11, 2010 at 5:37 pm
Thank you very much Eric,
February 18, 2010 at 11:30 am
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