SELECT sdb.Name AS DatabaseName ,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101), ‘-‘) AS LastBackUpTimeFROM sys.sysdatabases sdbLEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.nameGROUP BY sdb.Name

SET NOCOUNT ONGOSET quoted_identifier OFFDECLARE @dbname AS VARCHAR(80)DECLARE @msgdb AS VARCHAR(100)DECLARE @dbbkpname AS VARCHAR(80)DECLARE @dypart1 AS VARCHAR(2)DECLARE @dypart2 AS VARCHAR(3)DECLARE @dypart3 AS VARCHAR(4)DECLARE @currentdate AS VARCHAR(10)DECLARE @server_name AS VARCHAR(30)SELECT @server_name = @@servernameSELECT @dypart1 = DATEPART(dd,GETDATE())SELECT @dypart2 = DATENAME(mm,GETDATE())SELECT @dypart3 = DATEPART(yy,GETDATE())SELECT @currentdate= @dypart1 + @dypart2 + @dypart3PRINT "#####################################################################"PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"PRINT "#####################################################################"PRINT "DatabaseName Full Diff TranLog"PRINT "##########################################################################################################################################"SELECT SUBSTRING(s.name,1,50) AS ‘DATABASE Name‘,b.backup_start_date AS ‘Full DB Backup Status‘,c.backup_start_date AS ‘Differential DB Backup Status‘,d.backup_start_date AS ‘Transaction Log Backup Status‘FROM MASTER..sysdatabases sLEFT OUTER JOIN msdb..backupset bON s.name = b.database_nameAND b.backup_start_date =(SELECT MAX(backup_start_date)AS ‘Full DB Backup Status‘FROM msdb..backupsetWHERE database_name = b.database_nameAND TYPE = ‘D‘) -- full database backups only, not log backupsLEFT OUTER JOIN msdb..backupset cON s.name = c.database_nameAND c.backup_start_date =(SELECT MAX(backup_start_date)‘Differential DB Backup Status‘FROM msdb..backupsetWHERE database_name = c.database_nameAND TYPE = ‘I‘)LEFT OUTER JOIN msdb..backupset dON s.name = d.database_nameAND d.backup_start_date =(SELECT MAX(backup_start_date)‘Transaction Log Backup Status‘FROM msdb..backupsetWHERE database_name = d.database_nameAND TYPE = ‘L‘)WHERE s.name <>‘tempdb‘ORDER BY s.name

SELECTs.name, [Full DB Backup Status] = D, [Differential DB Backup Status] = I, [Transaction Log Backup Status] = LFROM sys.sysdatabases sLEFT JOIN (SELECT p.*FROM (SELECT [type], database_name, backup_start_dateFROM msdb.dbo.backupsetWHERE type IN (‘L‘,‘I‘,‘D‘)) tPIVOT (MAX(backup_start_date)FOR [type] IN ([L], [I], [D])) p) t ON s.name = t.database_nameORDER BY s.name