Chad Callihan takes us through our sordid pasts:
What do you do when you want to check the history of your database backups? Do you look at a drive containing your backups? Do you have software like Netbackup for you to open and review? My preference is to run a query in SSMS to get a quick overview.
With a couple tweaks this versatile script can be helpful in a variety of scenarios. You can check a single database, an entire SQL server, or multiple SQL servers. Here is how I use this type of script that queries the backupset table and backupmediafamily table in msdb. I recommend checking out these two tables because there are some good column options that you may want to include when reviewing backups. If you’re feeling dangerous, just modify the scripts below to be SELECT * and see what you get.
Click through for the scripts.