Full Recovery without Log Backups? Get Notified!

Why you need log backups in Full Recovery Model

Databases in the Full Recovery Model1 log every transaction into the transaction log file. This enables point-in-time recovery2, which can be critical for meeting recovery-point objectives. But, what if someone in your organization sets up a database in Full Recovery model but forgets to set up scheduled transaction log backups? This post shows how to query the MSDB database to notify you if a database misses a log backup for more than the specified time.

Full Recovery Is Possible

    Recovery of Orion – Certainly Not Database Recovery

The Script

Here’s the script:

The Even Better Script

The code above is a simple script that you could run in SSMS whenever you feel like it. I prefer to have this run via a SQL Server Agent Job, on a fairly frequent basis. Typically I have it run twice for each recovery-point-objective interval. i.e. If my recovery point objective is 30 minutes, I run the job every 15 minutes, to ensure I get notified quickly.

The code below ensapsulates the script from above into a SQL Server Agent Job named “Job Log Notifier”.

The job is scheduled to run every 60 minutes in the code above, and it reports any database in Full or Bulk Logged Recovery Model that hasn’t had a log backup in the past 60 minutes. Modify those parameters as you see fit. See the Microsoft documentation for the sp_add_jobschedule stored procedure for details on what parameters to change. Alternately, you could simply modify the job via the SQL Server Management Interface GUI for SQL Server Agent.

This post is part of our series on Database Recovery.

Let me know if you have any problems or questions concerning this script.


Notes:
1 – See Microsoft’s documentation on Recovery Models for details.
2 – Definition of Point in Time Recovery