Sql-server – Detached DB showing up in back-up history

backupsql server

I found a script to show me the databases that are not backed-up in the last 24 hours: Script to retrieve SQL Server database backup history and no backups

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, 
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), 
GETDATE()) AS  [Backup Age (Hours)] 
FROM    msdb.dbo.backupset 
WHERE     msdb.dbo.backupset.type = 'D'  
GROUP BY msdb.dbo.backupset.database_name 
HAVING      
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))  
 UNION  
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
 master.dbo.sysdatabases.NAME AS database_name,  
 NULL AS [Last Data Backup Date],  
 9999 AS [Backup Age (Hours)]  
 FROM 
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
 WHERE msdb.dbo.backupset.database_name IS NULL 
 AND  master.dbo.sysdatabases.name <> 'tempdb' 
 ORDER BY  
 msdb.dbo.backupset.database_name

The result shows me the DB that no logner exists. These are the DB that i have deleted/detached a while back. Why are these showing up?

DBs that are deleted

Best Answer

When you use Delete Database from modern versions of Management Studio, it asks whether you want to delete the backup history for this database in MSDB. The reason that a deleted database is showing in your query is simply because backup history exists for it - in MSDB.

I adjusted your query by joining backupset to sysdatabases, so it excludes databases that do not exist at the moment you run the query. 

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, 
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), 
GETDATE()) AS  [Backup Age (Hours)] 
FROM    msdb.dbo.backupset 
inner join master.dbo.sysdatabases
on msdb.dbo.backupset.database_name=master.dbo.sysdatabases.name
WHERE     msdb.dbo.backupset.type = 'D'  
GROUP BY msdb.dbo.backupset.database_name 
HAVING      
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))  
 UNION  
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
 master.dbo.sysdatabases.NAME AS database_name,  
 NULL AS [Last Data Backup Date],  
 9999 AS [Backup Age (Hours)]  
 FROM 
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
 WHERE msdb.dbo.backupset.database_name IS NULL 
 AND  master.dbo.sysdatabases.name <> 'tempdb' 
 ORDER BY  
 msdb.dbo.backupset.database_name
Related Question