Problem:- Sometimes , we are interested to know when last backup is taken for particular database ?
Solution: The first thing I want to share here that you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.name AS backupset_name,
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.
see below snap for detail
I hope this tip might help you somewhere.