--
set nocount on
go
declare @counter smallint
declare @dbname varchar(100)
declare @db_bkpdate varchar(100)
declare @status varchar(20)
declare @svr_name varchar(100)
declare @media_set_id varchar(20)
declare @filepath varchar(1000)
declare @filestatus int
declare @fileavailable varchar(20)
declare @backupsize float
select @counter=max(dbid) from master..sysdatabases
create table #backup_details
(
servername varchar(100),
databasename varchar(100),
bkpdate varchar(20) null,
backupsize_in_mb varchar(20),
status varchar(20),
filepath varchar(1000),
fileavailable varchar(200)
)
select @svr_name = cast(serverproperty('servername')as sysname)
while @counter > 0
begin
select @dbname = null , @db_bkpdate = null ,
@media_set_id = null , @backupsize = null ,
@filepath = null , @filestatus = null ,
@fileavailable = null , @status = null , @backupsize = null
select @dbname = name from master..sysdatabases where dbid = @counter
select @db_bkpdate = max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d'
select @media_set_id = media_set_id from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')
select @backupsize = backup_size from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')
select @filepath = physical_device_name from msdb..backupmediafamily where media_set_id = @media_set_id
exec master..xp_fileexist @filepath , @filestatus out
if @filestatus = 1
set @fileavailable = 'available'
else
set @fileavailable = 'not available'
if (datediff(day,@db_bkpdate,getdate()) > 7)
set @status = 'warning'
else
set @status = 'healthy'
set @backupsize = (@backupsize/1024)/1024
insert into #backup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable
update #backup_details
set status = 'warning' where bkpdate is null
set @counter = @counter - 1
end
select substring(servername,0,20) AS [ ],
substring(databasename,0,20) AS [ ],
rtrim(ltrim(bkpdate)) AS [ ],
rtrim(ltrim(backupsize_in_mb)) AS [ ],
rtrim(ltrim([status])) AS [ ],
substring(rtrim(ltrim(filepath)),0,40) AS [ ],
rtrim(fileavailable) AS [ ]
from #backup_details where databasename not in ('tempdb','northwind','pubs')
drop table #backup_details
set nocount off
go