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.

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/

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s