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

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: , ,
Posted in Administration, Backup

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

%d bloggers like this: