One of my colleague was looking for a script to identify the backup details for one his databases. Here is a very simple script to identify database backup details.

Purposes:

1. To confirm all the backup jobs are running fine at the defined intervals
2. To confirm the necessary backup strategies are in place For ex: For FULL recovery model requires frequent log backups to avoid the space issues
3. To identify the backup startdate and finishdate for backups
4. To identify the device name to which backups are initiated
5. To identify who initiated the backups(username)

SCRIPT

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   BkpSet.server_name,
   BkpSet.database_name, BkpSet.recovery_model,
   BkpSet.backup_start_date, BkpSet.backup_finish_date, 
   BkpSet.expiration_date, 
   CASE BkpSet.type  
       WHEN 'D' THEN 'Database(FULL)' 
       WHEN 'I' THEN 'Differential' 
       WHEN 'L' THEN 'Log'  
   END AS backup_type, BkpSet.backup_size,  
   BkpMF.logical_device_name,  BkpMF.physical_device_name,   
   BkpSet.name AS backupset_name, BkpSet.description, user_name
   FROM   msdb.dbo.backupmediafamily BkpMF
   INNER JOIN msdb.dbo.backupset BkpSet 
           ON BkpMF.media_set_id = BkpSet.media_set_id  
   WHERE  
   (CONVERT(datetime, BkpSet.backup_start_date, 102) >= GETDATE() - 7)  
   ORDER BY BkpSet.backup_finish_date desc

See Also:
Read more on SQL Server Job details:
https://latheeshnk.wordpress.com/2015/04/03/t-sql-script-get-sql-job-details-and-history-details/