sg05121983
asked on
Convert datetime into hours
Hi,
Below query returns if a backup hadn't occured in last 7 days. But my reuirement is to check for last 24 hours backup status. Please let me know, how to convert below query to check last 24 hours or 12 hours or 6 hours back backup status.
SELECT (SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinenam e'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancena me'))) as instance,
sd.name as name,
bs.type,
bs.database_name,
max(bs.backup_start_date) as last_backup,
note = CASE
WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
WHEN ISNULL(max(bs.backup_start _date),0) = 0 THEN 'ALERT'
ELSE '---'
END
FROM master..sysdatabases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
bs.type,
bs.database_name
Order by sd.name,last_backup
Thanks in Advance.
Below query returns if a backup hadn't occured in last 7 days. But my reuirement is to check for last 24 hours backup status. Please let me know, how to convert below query to check last 24 hours or 12 hours or 6 hours back backup status.
SELECT (SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinenam
sd.name as name,
bs.type,
bs.database_name,
max(bs.backup_start_date) as last_backup,
note = CASE
WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
WHEN ISNULL(max(bs.backup_start
ELSE '---'
END
FROM master..sysdatabases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
bs.type,
bs.database_name
Order by sd.name,last_backup
Thanks in Advance.
You can use DATEADD(HH, -24, GETDATE()) to check 24 hours ago.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
--