Query to list "last backup time" listing wrong times ( SQL Server 2008 R2 )

Question

I use this query to list last backup dates of our databases:

SELECT SDB.NAME AS DATABASENAME,
COALESCE(CONVERT(VARCHAR(12), MAX(BUS.BACKUP_FINISH_DATE), 101),'-') AS LASTBACKUPTIME
FROM SYS.SYSDATABASES SDB
LEFT OUTER JOIN MSDB.DBO.BACKUPSET BUS ON BUS.DATABASE_NAME=SDB.NAME
GROUP BY SDB.NAME
    ORDER BY 2 ASC

But today, it shows me "09/15/2015" for all databases, but we had a problem in the cluster, so the backup job didn't run. Why is this query listing "09/15/2015' if there are no backups for today?

last time the job run was 09/14/2015.

---

Edit 1:

With James Anderson's query, I got this too ( filtering with bckset.[type] ='D)

Again, I don't have backups from 2015-09-15, only 2015-09-14.

---

EDIT 2:

With some tests, I see that these queries shows me last backup date, even if I only have log backups. Is there a way to list "last full backup" date??

Answer 1

Your problem may be that the backup set was created but the backup did not complete. I use the query below to check my backups

USE MSDB;
GO

SELECT TOP  300
            bckset.database_name AS DatabaseName,
            bckmdiaset.physical_device_name AS BackupLocation,
            CASE 
                WHEN bckset.backup_size <= '10485760' THEN
                    CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
                ELSE
                    CASE 
                        WHEN bckset.backup_size <= '1048576000' THEN
                            CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
                        ELSE
                            CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
                    END
            END backupSize,
            CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
            CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
            CASE 
                WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
                    CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
                ELSE
                    CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
            END AS TimeTaken,
            CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
            CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
            CASE bckset.[type]
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential database'
                WHEN 'L' THEN 'Transaction Log'
                WHEN 'F' THEN 'File or filegroup'
                WHEN 'G' THEN 'Differential file'
                WHEN 'P' THEN 'Partial'
                WHEN 'Q' THEN 'Differential partial'
            END AS BackupType,
            bckset.server_name As ServerName,
            bckset.recovery_model As RecoveryModel,
            CASE bckset.is_snapshot
                WHEN '0' THEN 'FALSE'
                WHEN '1' THEN 'TRUE'
            END AS IsSnapshot,
            CASE [compatibility_level]
                WHEN 60 THEN 'SQL Server 6.0'
                WHEN 65 THEN 'SQL Server 6.5'
                WHEN 70 THEN 'SQL Server 7.0'
                WHEN 80 THEN 'SQL Server 2000'
                WHEN 90 THEN 'SQL Server 2005'
                WHEN 100 THEN 'SQL Server 2008'
                WHEN 110 THEN 'SQL Server 2012'
                WHEN 120 THEN 'SQL Server 2014'
            END AS CompatibilityLevel

FROM        msdb.dbo.backupset bckset
INNER JOIN  msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id

---WHERE bckset.database_name = ''

ORDER BY    backup_start_date DESC, backup_finish_date