Galera diariamente eu verifico a politica de backups do meu ambiente, desta forma a view abaixo me da uma visão geral dos horários e tipos de backups que foram realizados no dia.
CREATE VIEW [dbo].[VW_POLITICA_BACKUPS]
AS
SELECT ROW_NUMBER() OVER(ORDER BY vw.banco ASC) AS sequencia,vw.banco,vw.status_banco,vw.recovery_model,vw.backup_full, vw.backup_diferencial,vw.ultimo_log,
qtd_bancos
FROM (
SELECT DISTINCT UPPER(a.NAME) AS "banco",
a.state_desc status_banco,
CONVERT(SYSNAME, Databasepropertyex(a.NAME, 'Recovery'))
[recovery_model],
COALESCE((SELECT CONVERT(VARCHAR(19), Max(backup_finish_date),
121)
FROM msdb.dbo.backupset
WHERE database_name = a.NAME
AND type = 'D'
AND is_copy_only = '0'), 'APENAS BACKUP FULL') AS
'backup_full',
COALESCE((SELECT CONVERT(VARCHAR(19), Max(backup_finish_date),
121)
FROM msdb.dbo.backupset
WHERE database_name = a.NAME
AND type = 'I'
AND is_copy_only = '0'), 'APENAS BACKUP FULL') AS
'backup_diferencial',
COALESCE((SELECT CONVERT(VARCHAR(19), Max(backup_finish_date),
121)
FROM msdb.dbo.backupset
WHERE database_name = a.NAME
AND type = 'L'), 'APENAS BACKUP FULL') AS
'ultimo_log' ,
(select count(*) from sys.databases where NAME NOT IN ('tempdb','master','msdb','model') ) qtd_bancos
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = a.NAME
WHERE a.NAME NOT IN ('tempdb','master','msdb','model')
GROUP BY a.NAME,a.state_desc
) AS VWOcultei apenas a coluna com o nome dos meus bancos por segurança:
