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