TSQL Script to get Backup details in SQL Server

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.


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)


   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   BkpSet.database_name, BkpSet.recovery_model,
   BkpSet.backup_start_date, BkpSet.backup_finish_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  
   (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:

2 thoughts on “TSQL Script to get Backup details in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s