Latest Backups with TSQL

After rebooting a SQL Server, for whatever reason, it is always good to confirm that the system is running good.  There are many things that I check, one of which is to confirm that backups are running.

You can always go to the server browse to the various directories holding backups, and check on the latest files.  This can be difficult for servers with many databases.

Here is my quick check to confirm which backups are running on SQL Server. The query below gives you the 20 most recent backups that have been run.

SELECT TOP 20 database_name,
              type,
              backup_start_date,
              backup_finish_date,
              compressed_backup_size,
              backup_size
FROM   msdb.dbo.backupset
ORDER  BY backup_set_id DESC; 


In order to get the exact results you are looking for it may require you to add a where clause to filter on specific databases, or to expand the number of results in the TOP statement.

I hope that you find this as useful as I do.