September 7, 2010 at 2:32 pm
Good morning,
I have the following query that I would like to run from a central management server to check the backup status of all databases across all servers:
SELECT SD.Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup, case when Type = 'D' then 'Full' when type = 'L' then 'Log' when type = 'I' then 'Diff' else NULL end as Type,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet BS
Right outer join master..sysdatabases SD --check that databases still exist
on BS.Database_Name = SD.Name
WHERE NOT EXISTS(SELECT distinct database_name from MSDB.dbo.BackupSet where Type = 'D' and DATEDIFF(d, (Backup_Finish_Date), Getdate()) < 2) -- exclude databases where there is a full backup in the last day.
GROUP BY SD.Name, type
having DATEDIFF(d, max(Backup_Finish_Date), Getdate()) > 1
ORDER BY 4 desc
The problem is that it does not take into account differential backups that have taken place since the last full backup.
I would like it to show backups older than one day but don't show full backups that are older than one day if there is a diferential that took place in the last day. I thought maybe I need a case in the where but so far no luck.
Any suggestions to make this work properly?
September 8, 2010 at 10:32 am
I came up with this which seems to work:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
SELECT SD.Name, CONVERT(varchar(12), MAX(Backup_Finish_Date), 101) AS Last_Backup
,Type, convert (varchar(5),DATEDIFF(d, MAX(Backup_Finish_Date), GETDATE())) AS Days_Since_Last
INTO #temp FROM MSDB.dbo.BackupSet BS
RIGHT OUTER JOIN master..sysdatabases SD
ON BS.Database_Name = SD.Name
WHERE Type = 'D'
GROUP BY SD.Name, type
GO
INSERT INTO #temp
Select SD.Name, CONVERT(varchar(12), MAX(Backup_Finish_Date), 101) AS Last_Backup
,Type, convert (varchar(5),DATEDIFF(d, MAX(Backup_Finish_Date), GETDATE())) AS Days_Since_Last
FROM MSDB.dbo.BackupSet BS
RIGHT OUTER JOIN master..sysdatabases SD
ON BS.Database_Name = SD.Name
WHERE Type = 'I'
GROUP BY SD.Name, type
GO
INSERT INTO #temp (Name,Last_Backup,Type,days_since_last)
SELECT DISTINCT SD.Name,'NONE','',''
FROM [msdb].[dbo].[backupset] BS
RIGHT OUTER JOIN master..sysdatabases SD
ON BS.database_name = SD.name
WHERE BS.database_name IS NULL
GO
SELECT Name as [Database Name], Last_Backup as [Last Backup], case WHEN #temp.type = 'D' THEN 'Full'
WHEN #temp.type = 'I' THEN 'Diff' WHEN #temp.type = 'L' THEN 'Log'
ELSE '' end AS Type, days_since_last as [Days Since Last Backup] from #temp
WHERE Name not in ('master','model','msdb','tempdb')
AND (NOT EXISTS (SELECT database_name from MSDB.dbo.BackupSet BS
JOIN #temp ON BS.database_name=#temp.name WHERE #temp.type = 'I'
AND DATEDIFF(d,(Backup_Finish_Date), GETDATE()) < 2)
AND days_since_last > 2 OR Last_backup = 'NONE')
ORDER BY 4
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply