/***********************************************************

Script: Verifica backup
Descrição:
Retorna, para cada base de dados, as últimas datas para os backups FULL, Diferencial (Diff) e também retorna a data dos dois
últimos backups de log.
Compatibilidade: SQL Server 2005 e versões superiores

************************************************************/

SELECT
DISTINCT

@@servername as [Servidor],
a.Name AS [Banco de Dados] ,
CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) [Recovery Model] ,
COALESCE((SELECT CONVERT(VARCHAR(50), MAX(backup_finish_date), 121)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'D'
AND is_copy_only = '0'
), '-') AS 'Full' ,
COALESCE((SELECT CONVERT(VARCHAR(50), MAX(backup_finish_date), 121)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'I'
AND is_copy_only = '0'
), '-') AS 'Diferencial' ,
COALESCE((SELECT CONVERT(VARCHAR(20), MAX(backup_finish_date), 121)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'L'
), '-') AS 'Log (Último)' ,
COALESCE((SELECT CONVERT(VARCHAR(20), backup_finish_date, 120)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'L'
) withrownum
WHERE rownum = 2
), '-') AS 'Log (penúltimo)'
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE a.name <> 'TEMPDB'
GROUP BY a.Name