Script to Get Database Backup Time and Backup location..

We can use below T-SQL script to find the Database Backup Time and Backup location:

SELECT B.database_name,B.user_name As [User],
F.physical_device_name,B.backup_start_date as Backup_time,
CASE B.[Type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily F ON B.media_set_id = F.media_set_id
--WHERE B.database_name = DB_NAME() --uncomment this line for a signle Database
ORDER BY backup_start_date DESC
GO

Thanks!!!