--query 1
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
--query 2
-- for single db back up history
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
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,
'BackupType'=CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEn 'L' THEN 'Transaction Log'
END ,
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
ORDER BY backup_start_date desc,
backup_finish_date
