Backup status report

See more:

SQL-Server

I need a script that will check for success/failure after each type of backup from the backup history. Meaning, I want to know if the last full backup was successful/failed and if the last differential was successful/failed. I need it to check the last backup status of each type(full and diff) of backup.

What I have tried:

I have tried different scripts but they are not giving me the desired results. The closest script I found was written by Dathuraj Pasarge. I cant ell if it checks the status of each type of backup it reports. It seems to just report the status of the last backup. I need it to report the status of the last full backup and the last diff backup. Code below:
SET NOCOUNT ON
declare @check int
set @check=24
declare @FinalAge int
declare @hf int
declare @hd int
declare @servername nvarchar(60)
declare @dbname nvarchar(60)
declare @lastFullBackup datetime
declare @lastDiffBackup datetime
declare @NotBackedupSinceHrs int
declare @status nvarchar(30)

DECLARE @table1 table (Servername nvarchar(60), DBName nvarchar(60),

LastFullBackup datetime, LastDiffBackup datetime,NotBackedupSince int,[Status] nvarchar(30))

declare c1 cursor for

Select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),e.database_name) as DBname,
(Select convert(varchar(25),Max(backup_finish_date) , 100)
From msdb..backupset a
Where a.database_name=e.database_name
and a.server_name = @@servername and type='D'
Group by a.database_name) Last_FullBackup,

(Select convert(varchar(25),Max(backup_finish_date) , 100)
From msdb..backupset c
where c.database_name=e.database_name
and c.server_name = @@servername
and type='I' Group by c.database_name) Last_Diff_Backup,
NULL as NotBackedupSinceHrs,NULL as [Status]

From msdb..backupset e
Where e.server_name = @@Servername and
e.database_name not in ('tempdb')
and e.database_name in (Select Distinct name from master..sysdatabases where dbid<>2)

-- never backed up
Union all
select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),name) as DBname,
NULL, NULL,NULL as NotBackedupSinceHrs,NULL as [Status]

from master..sysdatabases as record

where name not in (select distinct database_name from msdb..backupset)and dbid<>2 order by 1,2

OPEN c1

FETCH NEXT FROM c1 INTO @servername,@dbname,@LastFullBackup,@LastDiffBackup,@NotBackedupSinceHrs,@status

WHILE @@FETCH_STATUS=0

BEGIN
IF (@LastFullBackup IS NULL)
BEGIN
set @LastFullBackup='1900-01-01 00:00:00.000'
END
IF (@LastDiffBackup IS NULL)
BEGIN
set @LastDiffBackup='1900-01-01 00:00:00.000'
END

select @hf=datediff(hh,@LastFullBackup,GETDATE())
select @hd=datediff(hh,@LastDiffBackup,GETDATE())

IF (@hf<@hd)
SET @FinalAge=@hf
ELSE
SET @FinalAge=@hd

INSERT INTO @table1 values (@servername,@dbname,@LastFullBackup,@LastDiffBackup,@FinalAge,@status)

FETCH NEXT FROM c1 INTO @servername,@dbname,@LastFullBackup,@LastDiffBackup,@NotBackedupSinceHrs,@status

END

UPDATE @table1 SET status = CASE
WHEN NotBackedUpSince <=@check THEN 'Success'
WHEN NotBackedUpSince > = @check THEN 'Failed, Action required !!!!'
END

UPDATE @table1 SET Status='Success'where DBName='master' and NotBackedUpSince< =@check +144

SELECT ServerName as 'SQLInstanceName',DBName as 'DatabaseName',LastFullBackup,LastDiffBackup,
NotBackedupSince as 'LastBackupHrs',Status FROM @table1 order by NotBackedUpSince desc

CLOSE c1
DEALLOCATE c1