The Enterprise Vault System Status information is very useful for giving you a heads-up of what sort of bad things might be happening in your environment. One of the common flags that System Status raises is around backups of the Enterprise Vault databases. The Enterprise Vault System Status page shows when the databases were last backed up, in fact it shows them all:
Fingerprint, or Vault Store Group database
The data relating to when the last good backup was performed is obtained by query a SQL Server system table, as shown below:
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
This query is from: http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/
There are lots of other very similar queries out on the internet, so it is worthwhile spending a few moments understanding the concept of the query, adapting it for your own use, and storing it away, ready for when or if it’s needed.
The output on my system looks like this: