Hi this is a awesome backup report
Made a couple of changes to so it did not return blank results and databases that do not require backups. ie tempdb
--Create a table variable and insert dbid from sysdatabases.
--Exclude temp database and exclude any databases that are not online
DECLARE @dbid table
(rowid INT IDENTITY(1,1),
mydbid int
)
insert into @dbid
SELECT dbid FROM master..sysdatabases where
name <> 'tempdb' and
databasepropertyex(name,'status') ='ONLINE'
and
DATABASEPROPERTYEX(name,'IsInStandBy') = 0 --Used for SQL Server 2005/2008 servers only
order by dbid
select @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(rowid) FROM @dbid)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
WHILE (@I <= @RowCount)
BEGIN
/* Need to re-initialize all variables*/
Select @dbName = null , @db_bkpdate = null ,
@media_set_id = Null , @backupsize = Null ,
@filepath = Null , @filestatus = Null ,
@fileavailable = Null , @status = Null , @backupsize = Null,
@database_id = mydbid
from @dbid where rowid = @I
select @dbname = name from master..sysdatabases where dbid = @database_id
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 DBA_TempBackup_details select @svr_name,@dbname,@db_bkpdate,@backupsize,@status,@filepath,@fileavailable
update DBA_TempBackup_details
set status = 'Warning' where bkpdate IS NULL
SET @I = @I + 1
END
🙂