Databases Missing Backups

Anytime we check someone’s SQL Server for the first time, we need to find out if backups have been happening.  Here are some queries that will help with that.

-- Databases without any backup history 
SELECT d.name
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name 
WHERE bs.database_name IS NULL 
AND d.name <> 'tempdb' 

-- Databases with no full or differential backups in last 24 hours
SELECT d.name 
FROM msdb.dbo.backupset bs
JOIN sys.databases d ON d.name  = bs.database_name
WHERE bs.type = 'D'  -- full
	OR bs.type = 'I' -- diff  
GROUP BY d.name 
HAVING (MAX(bs.backup_finish_date) < DATEADD(hh, -24, GETDATE()))  

-- Databases in FULL recovery Missing a TLog Back-Up Within Past 24 Hours 
SELECT d.name 
FROM msdb.dbo.backupset bs
JOIN sys.databases d ON d.name  = bs.database_name
WHERE d.recovery_model_desc = 'FULL'
	AND bs.type = 'L'  
	AND bs.database_name <> 'model'
GROUP BY d.name 
HAVING (MAX(bs.backup_finish_date) < DATEADD(hh, -24, GETDATE()))  

-- Databases in FULL recovery with a NUL TLog Back-Up Within Past 7 Days **TLOG has been truncated and not really backed up.
SELECT bs.database_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
JOIN sys.databases d ON d.name  = bs.database_name
WHERE d.recovery_model_desc = 'FULL'
	AND bs.type = 'L'  
	AND bmf.physical_device_name = 'NUL'
	AND bs.database_name <> 'model'
GROUP BY bs.database_name 
HAVING (MAX(bs.backup_finish_date) > DATEADD(DD, -7, GETDATE()))

One of my favorite queries to know when the last backup was done is from the estimable Glenn Berry’s scripts.  Glenn’s SQL Server Performance – Focused on improving SQL Server performance and scalability

-- Last backup information by database  (Query 9) (Last Backup By Database)
SELECT ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
       d.log_reuse_wait_desc AS [Log Reuse Wait Desc],
    MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
FROM sys.databases AS d WITH (NOLOCK)
LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
ON bs.[database_name] = d.[name] 
AND bs.backup_finish_date > GETDATE()- 30
WHERE d.name <> N'tempdb'
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name] 
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!