The following query shows the last time a full backup was taken on a certain database.
select database_name as [Database], a.name [NameOfBackup], user_name as [BackupTakenBy],
backup_start_date, backup_finish_date, backup_size as [SizeOfBackup], is_copy_only,
has_backup_checksums, b.physical_device_name as [LocationOfBackup]
from msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
where backup_set_id in
(select max(backup_set_id) from msdb.dbo.backupset where type='D' and is_copy_only in (0)
group by database_name)
This displays the last time a full backup was taken, as well as the user who took the last backup.
