MS SQL Server: Get Last Bakup Details For All Databases

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10),
DATABASEPROPERTYEX(name, 'status')) AS [Status],
CONVERT(VARCHAR(20), create_date, 103) + ' ' +
CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
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 dbName, [Last backup] DESC, NAME

database_id dbName Status         Creation date
-----------     -------     ----------      ----------------------
1                 testDB    ONLINE     08/04/2003 09:13:36

Last backup
--------------------------------------------------------------------------------------------
Full – 0 days ago – 03/11/2011 15:46:57 – 03/11/2011 15:46:57 (0 seconds)

Keep TSQling ;)

MS SQL Server: Get Complete Information of All Databases

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10),
DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE
DB_NAME(database_id) = DB.name AND
type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files
WHERE DB_NAME(database_id) = DB.name AND
type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE
DB_NAME(database_id) = DB.name AND
type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files
WHERE DB_NAME(database_id) = DB.name AND
type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE Compatibility_level
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' +
CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
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],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled'
ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'Autoclose'
ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'Readonly' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'Autoshrink'
ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'Auto create statistics'
ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'Auto update statistics'
ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'Standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'Cleanly shutdown'
ELSE '' END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

It will genterate the current Sql Server Database(s) resport in reference to each database.