As a Database Administrator, it is crucial to ensure that your SQL Server databases are properly backed up to prevent data loss. While regular full backups are important, transaction log backups are equally essential for point-in-time recovery and minimizing data loss in the event of a failure.

In this blog post, we will explore how to use SQL Server metadata to identify any databases running under the Full recovery model that have no transaction log backups listed in the system’s backup history.

Using the msdb System Tables

The msdb database in SQL Server stores information related to backup and recovery metrics, as well as SQL Server Agent job history and schedules. To identify missing transaction log backups, we can query the msdb.dbo.backupset system table.

Here is an example query that retrieves a list of databases running in Full recovery model without any recorded transaction log backups:

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 query retrieves the database name and recovery model description from the sys.databases view and joins it with the msdb.dbo.backupset table to check for any missing transaction log backups.

Identifying Databases with Full Backups but No Transaction Log Backups

In addition to identifying databases without any transaction log backups, it is also important to ensure that databases running in Full or Bulk-Logged recovery models have regular transaction log backups after a 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];

This query extends the previous one by joining with another subquery that retrieves the last full backup date for each database. It checks if there have been any transaction log backups after the last full backup. If not, it includes the database in the result set.

Implications of Missing Transaction Log Backups

Not issuing transaction log backups against databases running in Full or Bulk-Logged recovery models can have several implications:

• Transaction logs will not be check-pointed, and space will not be re-used, leading to log file growth.
• If autogrowth is enabled, log files will continue to grow, potentially consuming all available disk space.
• If autogrowth is not enabled, users may encounter errors stating that their transactions could not be fulfilled because the transaction log is full.

It is crucial to regularly monitor and ensure that transaction log backups are being performed to maintain database integrity and prevent potential issues.

Considerations

When using the provided queries, it is important to consider the interval at which you delete your backup history. Clearing backup history metadata on a daily basis may result in false positives. It is recommended to clear backup history information older than 31 days to obtain accurate results.

By regularly monitoring and addressing missing transaction log backups, you can ensure the availability and recoverability of your SQL Server databases.