backup status generate script

create view LastBackup as
     (select database_name,
     max(backup_finish_date) 'Last_Full_Backup'
from msdb.dbo.backupset
where type = 'D'
group by database_name)
go

create view LastLogBackup as
     (select database_name,
     max(backup_finish_date) 'Last_Log_Backup'
from msdb.dbo.backupset
where type = 'L'
group by database_name)
go

set nocount  on
PRINT @@servername
PRINT 'Databases not backed up'
go
select convert(varchar(18),name) Name,ltrim(rtrim(convert(varchar(10),DATABASEPROPERTYEX(name, 'Updateability')))) Status
from master.dbo.sysdatabases
where name COLLATE DATABASE_DEFAULT not in (select database_name from LastBackup) and name <> 'tempdb'
go

PRINT ' '
PRINT 'Databases backup up'
go
select convert(varchar(18),a.database_name) Database_Name, ltrim(rtrim(convert(varchar(10),DATABASEPROPERTYEX(a.database_name, 'Updateability')))) Status,a.Last_Full_Backup, b.Last_Log_Backup
from LastBackup a
left outer join LastLogBackup b
on a.database_name = b.database_name
where a.database_name COLLATE DATABASE_DEFAULT in (select name from master.dbo.sysdatabases)
order by a.Last_Full_Backup
go
drop view LastBackup
go
drop view LastLogBackup
go