Wednesday, November 18, 2009

How to check details of Most Recent Backups and # of days since ANY type of backup on any SQL server

SELECT as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = --AND A.type = 'D'
where not like '%skip these%'
GROUP BY , a.type, status
ORDER BY , LastBackupDate desc,a.type, status

No comments:

Post a Comment