O ultimo backup full executado com sucesso

O script executa a leitura na tabela backupset do banco de dados msdb procurando pelo último backup full completo.

DECLARE @LastFullBackupTable TABLE
    (
      name VARCHAR(256) ,
      LastFullBackupDate DATETIME
    )

INSERT  INTO @LastFullBackupTable
        ( name ,
          LastFullBackupDate
        )
        SELECT  QUOTENAME(db.name) ,
                MAX(backup_finish_date)
        FROM    sys.databases AS db
                JOIN msdb.dbo.backupset (NOLOCK) AS BkpSet 
                ON BkpSet.database_name = db.name
        WHERE   type = 'D'
        GROUP BY db.name
        ORDER BY MAX(backup_finish_date) DESC

SELECT  db.name ,
        ISNULL(tmp.LastFullBackupDate, '') AS LastFullBackup
FROM    @LastFullBackupTable AS tmp
        RIGHT JOIN sys.databases AS db ON tmp.name = QUOTENAME(db.name)
        WHERE db.name <> 'tempdb'