SQL Query to find database backup details

---Query to find the backup media set and backup meda info, size and duration information and type.

SELECT TOP 100

s.database_name,

--m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BKP_Size,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

--CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

--CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

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 Backup_start_Date > '2013-06-27'

--WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY backup_start_date DESC, backup_finish_date