Full recovery mode databases which are not backedup in last 24 hours

1

Hi Team,
please help me with the query to check my production databses which are in full recovery, but still not part of log backups.

please help me with the query.

1 Like
2

The info you need is in the msdb database in backup*** tables. Run the following query:

;WITH cte AS
(
	SELECT type ,
		   database_name ,
		   MAX(backup_finish_date) LatestBackupTime
	FROM   msdb.dbo.backupmediafamily f
		   INNER JOIN msdb.dbo.backupset b ON 
			f.media_set_id = b.media_set_id
	GROUP BY 
		type, database_name
)
SELECT c1.*
FROM
	cte c1
WHERE
	c1.type = 'D'
	AND NOT EXISTS
    (
		SELECT *
		FROM cte c2
		WHERE 
			c2.database_name = c1.database_name
			AND c2.type = 'L'
	);
2 Likes
3 
SELECT database_name,
    MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS Last_Full_Backup,
    MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS Last_Log_Backup,
    MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset
GROUP BY database_name
ORDER BY CASE WHEN MAX(CASE WHEN type = 'L' THEN backup_finish_date END) IS NULL 
    THEN 0 ELSE 1 END, database_name
2 Likes
4

simple recovery databases should not be shown in this. somehow, they are also showing in out put. please help.

1 Like
5

SELECT bs.database_name,
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS Last_Full_Backup,
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS Last_Log_Backup,
MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases db ON db.name = bs.database_name
WHERE db.recovery_model_desc <> 'SIMPLE'
GROUP BY bs.database_name
ORDER BY CASE WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL
THEN 0 ELSE 1 END, bs.database_name

1 Like
6

it is very close. thank you. but my requirement is:
I want to identify the list of databases, which are not receiving log backups. the Query is giving all log backups. please could you tweak once more.

7 
;WITH cte AS
(
	SELECT type ,
		   database_name ,
		   MAX(backup_finish_date) LatestBackupTime
	FROM   msdb.dbo.backupmediafamily f
		   INNER JOIN msdb.dbo.backupset b ON 
			f.media_set_id = b.media_set_id
			INNER JOIN sys.databases d ON
				d.name = b.database_name
	WHERE
		d.recovery_model_desc NOT IN ( 'SIMPLE' )
	GROUP BY 
		type, database_name
)
SELECT c1.*
FROM
	cte c1
WHERE
	c1.type = 'D'
	AND NOT EXISTS
    (
		SELECT *
		FROM cte c2
		WHERE 
			c2.database_name = c1.database_name
			AND c2.type = 'L'
	);
1 Like
8

Seems it is giving full backups. not log backups

9

Something like this?

    Use msdb;
     Go

 --==== Get list of databases
   With databaseList
     As (
 Select d.name
   From sys.databases            d
  Where d.recovery_model_desc <> 'SIMPLE'
        )
 --==== Get Log Backups for all databases
      , logBackups
     As (
 Select bs.database_name
      , bs.backup_finish_date
      , rowNumber = row_number() over(Partition By bs.database_name Order By bs.backup_finish_date desc)
   From dbo.backupset           bs
  Where bs.[type] = 'L'
        )
 --==== Identify databases with no log backup in past 24 hours
 Select *
   From databaseList            dl
   Left Join logBackups         lb On lb.database_name = dl.name
                                  And lb.rowNumber = 1
  Where lb.backup_finish_date Is Null
     Or lb.backup_finish_date < dateadd(hour, -24, getdate());
2 Likes
10

No log backups at all? Here it is:

SELECT bs.database_name,
MAX(CASE WHEN bs.type = ‘D’ THEN bs.backup_finish_date END) AS Last_Full_Backup,
MAX(CASE WHEN bs.type = ‘L’ THEN bs.backup_finish_date END) AS Last_Log_Backup,
MAX(CASE WHEN bs.type = ‘I’ THEN bs.backup_finish_date END) AS Last_Diff_Backup
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases db ON db.name = bs.database_name
WHERE db.recovery_model_desc <> 'SIMPLE’
GROUP BY bs.database_name
HAVING MAX(CASE WHEN bs.type = ‘L’ THEN bs.backup_finish_date END) IS NULL
ORDER BY CASE WHEN MAX(CASE WHEN bs.type = ‘L’ THEN bs.backup_finish_date END) IS NULL
THEN 0 ELSE 1 END, bs.database_name

The ORDER BY I used above would list all dbs without a log backup first, then list the ones that did. Thus, if the first row has a log backup date, then there were no dbs without a log backup.

At any rate, are you sure you don't want those with no log backup after the last full backup? That is, where perhaps the most recent log backup(s) is(are) missing, even if a log backup occurred earlier.

2 Likes