SQLINFO – We play with data and technology

In previous post we discussed about a script which will give the restore information. In this post we will discuss about a script which will give the backup information.

SELECT server_name        AS [Servername], 
       database_name      AS [Databasename], 
       [user_name]        AS [BackupUser], 
       backup_start_date  AS [BackupStartDate], 
       backup_finish_date AS [BackupFinished], 
       CASE 
         WHEN type = 'D' THEN 'FullBackup' 
         WHEN type = 'I' THEN 'Differential' 
         WHEN type = 'L' THEN 'LOG' 
         WHEN type = 'F' THEN 'Partial' 
         WHEN type = 'Q' THEN 'Differential partial' 
         ELSE NULL 
       END                AS [BackupType], 
       MF.physical_device_name, 
       CASE 
         WHEN device_type = 2 THEN 'Disk' 
         WHEN device_type = 5 THEN 'Tape' 
         WHEN device_type = 7 THEN 'VirtualDevice' 
         WHEN device_type = 105 THEN 'A Permanant Backup Device' 
         ELSE NULL 
       END                AS [BackupDevice] 
FROM   msdb.dbo.backupset Bs 
       INNER JOIN msdb.dbo.backupmediafamily MF 
               ON Bs.media_set_id = MF.media_set_id 
ORDER  BY backupfinished DESC

This script will give the following information like backup start and end date, Type of backup, backup device, path of backup etc. Let us look into the screen shot