Script to find SQL Server databases without transaction log backups

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


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.


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];
query results

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.

D.[name] AS [database_name], D.[recovery_model_desc] 
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]
   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]
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford 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

Comments For This Article

Wednesday, January 6, 2010 - 12:42:23 PM - SandraV Back To Top (4662)

Do you want parenthesis around the test for BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date]?

Without them, this returns one of my DBs that's in SIMPLE model but was previously FULL because it has a T-Log backup in its history from back in May and then a Full DB backup from today.

Wednesday, January 6, 2010 - 9:02:55 AM - timmer26 Back To Top (4657)

I'm pleased that you found the tip useful.  Your comments are quite interesting in that I wrote the tip from the angle of identifying databases that need to have log backups configured.  We sometimes fail to forget though that there are some shops where you only need to have the option to recover to the last good full database backup and, indeed, the script works for that as well.

Wednesday, January 6, 2010 - 7:05:07 AM - --cranfield Back To Top (4652)

Thanks for the tip and the extra info on the msdb system tables.  I often find that databases are created in FULL mode unnecessarily where SIMPLE would do just fine and all you need is a daily full backup. Reason that so many databases get created in FULL mode is probably due to the Model database being FULL.  As a standard I always change databases to SIMPLE mode and create a daily full backup job. Only if there is a point-in-time recovery requirment will I implement FULL with tran log backups.

I also see DBAs forgetting to implement tran log backups on the Principal when implementing mirroring. Your approach will identify where this is happening.



get free sql tips
agree to terms