Friday, August 12, 2011

All SQL Server Backups taken Last time

-- All SQL Server Backups taken Last time
SELECT d.name AS DatabaseName,
MAX(b.backup_finish_date) AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D' -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model GROUP BY d.name
ORDER BY 2 DESC
--Show details of the successful Backup operations performed on this server in the past

SELECT d.name AS DatabaseName,
b.backup_finish_date AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type IN( 'D') -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model ORDER BY 2 DESC