Warning : This section is under contruction

Script 1: Get Last backups

select @@servername as ServerName, A.*
from
( select

ROW_NUMBER() OVER ( PARTITION BY sd.name, bs.Type ORDER BY bs.backup_start_date DESC ) AS 'RowNumber',
sd.name as DBName,
sd.recovery_model_desc as RecoveryModel,
bs.Type,
bs.backup_start_date,
bs.backup_finish_date,
datediff(hour, bs.backup_finish_date, getdate()) as ElaspsedTimeInHour,
datediff(minute, bs.backup_start_date, bs.backup_finish_date) as DurationInMin,
bm.Physical_Device_name
from master.sys.databases sd
left join msdb.dbo.backupset bs on db_id(bs.database_name) = sd.database_id -- on bs.database_name = sd.name collate SQL_Latin1_General_CP1_CI_AS
left join msdb.dbo.backupmediafamily bm on bm.media_set_id = bs.media_set_id
where (sd.name not in ('tempdb'))
and (bs.Type in('D', 'L', 'I') or bs.Type is null) -- D:full, I:Diff. L:Log
)
A
where A.RowNumber <= 1
order by A.DBName, A.Type, A.backup_start_date desc
go