Questions that seem to come up time and time again are:

• Did the databases on Server X get backed up last night? / When was the last time each database was backed-up successfully?
• What if someone creates a new database on your existing server and doesn’t add it to a maintenance plan – how would you ever find out that it wasn’t being backed up?
• What if someone adds a new SQL instance or whole new SQL server – how would you ensure you were checking the backups on that as well?

Part 1 in this series will cover off how you get this data using SQL Management Studio (on a single server), while Part 2 will focus on using PowerShell to automate getting the database backup state (across all SQL servers).

The history of all successful backups is kept in the msdb database, in a table called dbo.backupset. A simple SELECT * statement or even right-click and Select Top 1000 Rows will show you the currently stored history:

(scroll to the far right hand side of the returned results)

This is a good start – we can see the backup finish dates, backup sizes and names of the databases being backed up. We now need to eliminate a few things and make the results more meaningful:

• I only want to find full backup results, not transaction log backups or differential backups. (Type D is for full database backups)
• There are records for the same server multiple times (historical results).  I only want the most recent backup time-stamp returned.

To narrow down the results to just D type backups, let’s add-in a WHERE clause.  To get only the latest result, we need to use the MAX() function on the last backup date and group by the database name.  The query and results then look like this:

Looks good, with one problem.  The above table only shows successful backups. If a database has never been backed up (or never been backed up successfully) it won’t show in this list.  So, we need to JOIN these results with a table of all databases.

A list of all databases is kept inside the sys.databases table:

Now, joining those two queries together, we get:

Now we can clearly see which databases have never been backed up (or whose backup history has been purged), as well as all other databases and their last successful backup date.

And here’s the query, so that you can copy/paste and try it yourself:

References
https://msdn.microsoft.com/en-us/library/ms178534.aspx
https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx