SQL Server: What exactly is the backup_finish_date in master.sys.databases

Question

When querying for the last backup_finish_date with the following query (from SQL Server: how to query when the last transaction log backup has been taken?):

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DESC

The backup_finish_date for all my databases is null, this is for DBs with a recovery mode of BULK_LOGGED, FULL or SIMPLE.

Does this imply that none of these databases has had their transaction log backed up (as suggested by the title of the linked question)?

Answer 1

Yes, You are correct. If your backup_finish_date is null means it was never backed up. However, msdb..backupset can be modified/tempered. You can check last backup of the database with backup type.

SELECT   d.name,
         d.recovery_model_desc,
         b.type, -- type of backup
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
GROUP BY d.name, d.recovery_model_desc, b.type
ORDER BY backup_finish_date DESC

type Can be:
D = Database OR Full
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Refer MSDN