I'm needing assistance completing the following code. This query needs to run the number of hours since last successful tlog backup. There are 3 cases when I get a return, No Db on the instance, No Tlog has ever run and Tlog is less than 1hr old. For any and all cases, I need the results to return the number 0. Any ideas are appreciated.
declare @dbcount int = (select count(*) from master.sys.databases where name not in ('tempdb', 'master', 'model', 'msdb'))
if (@dbcount = 0)
select 0 as [backup age (hours)]
select top 1
case when max(bs.backup_finish_date) is null then datediff(hh, max(bs.backup_finish_date), getdate())
as [backup age (hours)]
join master.sys.databases d
on d.name = bs.database_name
bs.type = 'L' and
database_name not in ('tempdb', 'master', 'model', 'msdb') and d.state = 0 and d.source_database_id is null
(max(bs.backup_finish_date) < dateadd(MINUTE, - 15, getdate()))
- This topic was modified 3 months, 3 weeks ago by rr_littletons.
- This topic was modified 3 months, 3 weeks ago by rr_littletons. Reason: Update Title and move to new form