Backup Status - SQL Server

Step I:

Create Table to see the status of particular database backup.

USE [dbmonitor]
GO

/****** Object: Table [dbo].[backupdb] Script Date: 06/18/2009 14:40:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[backupdb](
[name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Step II:
Insert your required monitoring database name in to the above table
Step III:
Create procedure P_Backupstatus
as
Begin
Declare @servername varchar(60)
Set @servername=@@servername
--Print @servername
SELECT machine_name as [Machine Name],Database_Name as [Database Name],
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as [Last Backup],
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as [Days Since Last]
FROM MSDB.dbo.BackupSet
WHERE Type = 'd' and machine_name=@servername and database_name in (select name from dbmonitor.dbo.backupdb) --and (Database_Name='dbmonitor')
GROUP BY machine_name,Database_Name
ORDER BY 3 DESC
End

Step IV:

You can see the backup status.