Get backup status for a SQL database (T-SQL)

Select database_name As [Database Name]
  , [name] As [Backup Name]
  , Case When Type = 'D' Then 'Full Backup'
    When Type = 'I' Then 'Differential Backup'
    When Type = 'L' Then 'Log Backup'
    Else 'File or filegroup or partial'
   End As [Backup Type]
  , Recovery_Model
  , [backup_start_date] As [Time of the SQL Backup Job]
FROM [msdb].[dbo].[backupset]
 where database_name = 'Database' and type = 'D'       
         -- Insert DB name on row above