Script to find SQL Server databases without transaction log backups

By: Tim Ford   |   Comments (3)   |   Related: 1 | 2 | 3 | > Backup

Problem

This is an opportunity for me to geek out on my favorite topic in Microsoft SQL Server: mining SQL Server metadata.  If you've read my tips over the past two years you may have seen that I am a metadata junkie.  This was developed from a need to automate as many tasks as possible over the past 9 years as the solo Database Administrator in my organization.  At a high point, we had 2,000 databases hosted across 80 SQL Server instances (all on separate servers).  We had one Database Administrator, Me. 

Thankfully, through consolidation, we've pared down the number of instances by 50% and I now have help through access to 30% of one of our Oracle DBAs.  Though things have improved, I still need to rely on automation and creativity in order to accomplish all the administrative tasks that confront a DBA on any given day.  We all know the unexpected issues that arise will do their best to destroy any forward progress you make on a daily basis. 

In this case it is the use of the System Tables from the msdb database to identify any databases, running under Full recovery, that have no log backups listed in the system's backup history.  In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.

Solution

Usually I advocate utilizing the Dynamic Management Objects to mine system data from Microsoft SQL Server.  However, backup history is not something stored in the Dynamic Management Views and Dynamic Management Functions (DMVs and DMFs, respectively.)  This information is stored in the msdb database. 

The msdb database is used to host information pertaining not to just backup and recovery metrics, but also all things SQL Server Agent: job history, job schedules, and so forth.  It is this backup information we're most-interested in here.

Query 1

We can get all the information for this task out of a single msdb System Table:  msdb.dbo.backupset.  The important columns of note for this object are itemized below.  Asterisks denote the columns we'll be using in this query:

• *database_name - name of the database the backup file pertains to.
• *type - Type of backup process performed:
  • D = Database
  • F = File or Filegroup
  • G = Differential File
  • I = Differential (database)
  • L = Transaction Log
• *backup_finsh_date - time when the backup process completed.
• backup_start_date - time the backup process was initiated.
• name - name of the backupset
• user_name - user performing the backup process
• expiration_date - date the backup expires

SELECT D.[name] AS [database_name], D.[recovery_model_desc]
FROM sys.databases D LEFT JOIN 
   ( 
   SELECT BS.[database_name], 
       MAX(BS.[backup_finish_date]) AS [last_log_backup_date]
   FROM msdb.dbo.backupset BS 
   WHERE BS.type = 'L'
   GROUP BY BS.[database_name]
   ) BS1 ON D.[name] = BS1.[database_name]
WHERE D.[recovery_model_desc] <> 'SIMPLE'
   AND BS1.[last_log_backup_date] IS NULL
ORDER BY D.[name];

This code queries msdb.dbo.backupset for a listing of databases that have recorded log backups and when the latest log backup occurred.  The results are then joined via an outer join construct back to the sys.databases System Catalog View on database_name.  Any database that exists on the SQL Server (as presented through the sys.databases view) running in Full or Bulk-Logged recovery, that does not exist in the list of databases with associated transaction log backups, is returned as a result of the query.

Query 2

Here is another query that will show you any databases that are in the Full or Bulk-Logged recovery model and have had a full backup without any transaction log backups after the last full backup.

SELECT 
D.[name] AS [database_name], D.[recovery_model_desc] 
FROM 
sys.databases D LEFT JOIN 
( 
   SELECT BS.[database_name], 
   MAX(BS.[backup_finish_date]) AS [last_log_backup_date] 
   FROM msdb.dbo.backupset BS 
   WHERE BS.type = 'L' 
   GROUP BY BS.[database_name] 
   ) BS1 
ON D.[name] = BS1.[database_name]
LEFT JOIN 
( 
   SELECT BS.[database_name], 
   MAX(BS.[backup_finish_date]) AS [last_data_backup_date] 
   FROM msdb.dbo.backupset BS 
   WHERE BS.type = 'D' 
   GROUP BY BS.[database_name] 
) BS2 
ON D.[name] = BS2.[database_name]
WHERE 
D.[recovery_model_desc] <> 'SIMPLE'
AND BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
ORDER BY D.[name];

What are the implications of not issuing log backups against a database running in Full or Bulk-Logged recovery?  Without log backups, transaction logs are not going to be check-pointed and space will not be re-used.  Log files will grow (if Autogrowth is enabled) and space will eventually be consumed to a point that the SQL Server is unable to allocate space as needed where databases are set to Autogrow.  If Autogrowth is not enabled on identified databases, your users will eventually receive an error stating that their transactions could not be fulfilled because the transaction log is full.

What impacts the results?  The interval at which you delete your backup history will directly influence what results are presented through this query.  If you clear backup history metadata on a daily basis you may return false positives.  I strongly suggest clearing backup history information from your instances for any records over 31 days.  This not only will allow you to run queries like the one I present here and obtain decent, usable information; but it also ensures you do not encounter situations where your msdb database has grown to a significant size that could impact backup and recovery performance or space issues on your SQL Server instance.

Next Steps

• Read additional SQL Server backup tips

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips