Find out the creation and backup date of each database in the server:

Find out the creation and backup date of each database in the server

select database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],

-- last backup

ISNULL((SELECT TOP 1

CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +

CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +

CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +

' (' + CAST(DATEDIFF(second, BK.backup_start_date,

BK.backup_finish_date) AS VARCHAR(4)) + ' '

+ 'seconds)'

FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]

FROM sys.databases DB

order by 'Last backup' desc