November 11, 2014 at 1:24 pm
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online.
I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Returns many values of 1.
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE())
But get the aggregate in where clause error.
Anyone able to help me get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
November 12, 2014 at 12:30 am
Try removing the group by completely:
select count(distinct msdb.dbo.backupset.database_name)
from msdb.dbo.backupset
where datediff(day, backup_finish_date, getdate()) > 1 -- or is null
and database_name not in ('tempdb', 'ReportServerTempDB', 'AdventureWorksDW', 'AdventureWorks') --online also
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2014 at 9:41 am
If this helps, I install the following procedure on all SQL Server instances I set up and create an agent job which runs the proc each morning to tell me what databases have not been backed up for a day, or not backed up at all.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupCheckNotification] Script Date: 12/11/2014 16:36:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupCheckNotification]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Data AS TABLE
(
ID INT IDENTITY(1, 1),
DBName NVARCHAR(128),
LastBackupDate DATETIME
)
DECLARE @BackupsNotDone AS TABLE
(
ID INT IDENTITY(1, 1),
DBName NVARCHAR(128),
LastBackupDate DATETIME
)
DECLARE @mailBody NVARCHAR(MAX);
DECLARE @mailSubject NVARCHAR(100);
DECLARE @cnt INT,
@count INT;
DECLARE @DataRecord NVARCHAR(500);
SET @cnt = 0;
SET @count = 1;
SET @mailSubject = 'Full Database Backups Failing on Server: '
+ @@SERVERNAME;
SET @mailBody = 'The following database(s) running on ' + @@servername
+ ' have not been backed for over a day. <p>'
INSERT INTO @Data
SELECT database_name,
COALESCE(CONVERT(VARCHAR(25), MAX(backup_finish_date), 113),
'Backup Not Taken')
FROM msdb.dbo.backupset
WHERE type = 'D'
AND database_name IN ( SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' )
GROUP BY database_name
UNION
SELECT name,
'1900-01-01 00:00:00.000' AS 'Backup Not Taken'
FROM sys.databases
WHERE name NOT IN ( SELECT database_name
FROM msdb.dbo.backupset
WHERE type = 'D'
GROUP BY database_name )
AND name != 'TempDB'
INSERT INTO @BackupsNotDone
SELECT DBName,
LastBackupDate
FROM @Data
WHERE LastBackupDate < DATEADD(DAY, -1, GETDATE())
SELECT @cnt = COUNT(*)
FROM @BackupsNotDone;
IF @cnt > 0
BEGIN
WHILE @count <= @cnt
BEGIN
SET @DataRecord = '';
DECLARE @backupDate VARCHAR(25);
SET @backupDate = '';
SELECT @backupDate = CASE LastBackupDate
WHEN '1900-01-01 00:00:00.000'
THEN 'Never Backed Up'
ELSE CONVERT(NVARCHAR(25), LastBackupDate, 113)
END
FROM @BackupsNotDone
WHERE ID = @count;
SELECT @DataRecord = '<b>' + DBName
+ ' </b> - Last Back Up Date: ' + @backupDate
FROM @BackupsNotDone
WHERE ID = @count;
SET @mailBody = @mailBody + '
' + @DataRecord;
SET @count = @count + 1;
END
-- Email out the notification:
EXEC msdb.dbo.sp_send_dbmail @recipients = N'mail@mail.com', -- change to valid mail address
@subject = @mailSubject, @body = @mailBody,
@profile_name = 'DefaultMailProfile', -- change to valid mail profile
@body_format = HTML;
END
END
Produces nice output emails like the following:
"The following database(s) running on ServerOne\Corp have not been backed for over a day.
master - Last Back Up Date: 07 Nov 2014 22:51:38:000
model - Last Back Up Date: 07 Nov 2014 22:51:38:000
msdb - Last Back Up Date: Never Backed Up"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply