SQL Server 2012 – Get Backup History of the database

The msdb database is system database used by SQL Server Agent for scheduling alerts and jobs.

msdb.dbo.backupset – Contain the information concerning the most-granular details of the backup process
msdb.dbo.backupmediafamily – Contains the metadata for the physical backup files as they relate to backup sets

Using the above mentioned tables, we will find the backup history of database

Script:

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' 
AS Backup_Size,
CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) 
AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this where clause for all  database
ORDER BY backup_start_date DESC, backup_finish_date
GO

You are done!