There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.
The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.
/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */ SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date' FROM master.sys.databases d JOIN msdb.dbo.backupset b ON d.name = b.database_name WHERE (b.type = 'D' OR b.type = 'I') GROUP BY d.name ORDER BY d.name
The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.
This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.
/* GET LAST BACKUP DATE FROM BACKUPSET */ SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date' FROM msdb.dbo.backupset WHERE (type = 'D' OR type = 'I') GROUP BY database_name ORDER BY database_name
The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.
The easiest way to determine which databases no longer exists is to execute the following.
/* RETURN NON EXISTING DATABASE NAMES */ SELECT DISTINCT database_name FROM msdb.dbo.backupset WHERE ( database_name NOT IN ( SELECT name FROM master.sys.databases ) )
The results indicate that (4 row(s) affected) and the names of the database that are not current.