How to find databases without transaction log backups [SQL Server]

In my previous post Last backup of all databases, I had sent a little tip to find details about status of last backup Full.
Here is a query to show you the databases that are in Bulk-Logged and Full Recovery model where there are a Full Backup without any transaction log backups.

Possible problems
Without log backups, the transaction logs will not be check-pointed. So, the spaces will not be reused causing high growth on logfiles.


SELECT            
'DB without transaction log bkps' [.],
D.[name] as [database], D.[recovery_model_desc] as [recovery_model],
BS1.[last_log_backup_date] ,BS2. [last_data_backup_date]
FROM            
(sys. databases D left outer JOIN             
(            
   SELECT BS .[database_name],                
   MAX( BS.[backup_finish_date] ) AS [last_log_backup_date]             
   FROM msdb .dbo. backupset BS
   INNER JOIN sys .sysdatabases sys on sys . name = BS . [database_name]  
   WHERE BS .type = 'L'
   GROUP BY BS. [database_name]             
   ) BS1             
ON D. [name] = BS1 .[database_name]            
LEFT OUTER JOIN             
(            
   SELECT BS .[database_name],              
   MAX( BS.[backup_finish_date] ) AS [last_data_backup_date]             
   FROM msdb .dbo. backupset BS
   INNER JOIN sys .sysdatabases sys on sys . name = BS . [database_name]  
   WHERE BS .type = 'D'
   GROUP BY BS. [database_name]             
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s