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.
Recovery of Orion – Certainly Not Database Recovery
The Script
Here’s the script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
DECLARE @msg nvarchar(max); SET @msg = N''; ;WITH src AS ( SELECT bs.database_name FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE bs.type = 'L' --only log backups AND bs.backup_finish_date > DATEADD(MINUTE, 0 - 120, GETDATE()) ) , last_backup AS ( SELECT bs.database_name , bs.backup_start_date , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC) FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE bs.type = 'L' --only log backups AND bs.backup_finish_date <= DATEADD(MINUTE, 0 - 120, GETDATE()) ) SELECT @msg = @msg + CASE WHEN @msg = N'' THEN N'' ELSE N'</p></td></tr><tr><td style="padding-left:10px;">' END + d.name + N'</p></td><td style="padding-left:20px;">' + COALESCE(CONVERT(nvarchar(30), lb.backup_start_date, 120), '') FROM sys.databases d LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1 WHERE d.state_desc = N'ONLINE' AND d.recovery_model_desc <> N'SIMPLE' --simple recovery affords no log backups AND d.name <> CONVERT(sysname, N'model') AND NOT EXISTS ( SELECT 1 FROM src WHERE src.database_name = d.name ) ORDER BY d.name; IF @msg <> N'' BEGIN SET @msg = N'<!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Log Backup Notification from ' + CONVERT(nvarchar(128), @@SERVERNAME) + '</title> <style> body { font-family: Segoe UI,Verdana,Tahoma; font-size: 10pt; } thead tr td { background-color: #948d8d; padding: 2px; border: solid; border-color: black; border-width: 2px; margin: 0px; text-align:center; } tbody tr td { background-color: #eeeeee; padding-left: 5px; padding-right: 5px; padding-bottom: 2px; border-width: 0px; margin: 0px; } tbody tr { padding-bottom: 0px; padding-top: 0px; } </style> </head> <body> The following databases haven''t had a log backup during the last 120 minutes:</p><br /> <table><thead><tr><td style="width: 350px;">Database</p></td><td style="width: 150px;">Last Log Backup</p></td></tr></thead><tbody><tr><td style="padding-left:10px;"><p>' + @msg + N'</p></td></tr></tbody></table> <p>Only databases in FULL or BULK LOGGED recovery model are listed in this email. Please ensure the appropriate transaction log backup job is scheduled for the listed databases, or investigate changing their recovery model to simple. Only change the recovery model to simple if this is a non-critical, non-production database. Simple recovery model prevents point-in-time recovery, and may make our recovery-point-objective impossible to meet. <p style="font-size:9pt;">This email was sent from the [Log Backup Notifier] SQL Server Agent Job on YELLER\ONGOLE. </body> </html>'; DECLARE @subject nvarchar(1000); SET @subject = 'Log Backup Notification from SQL Server: ' + @@SERVERNAME; EXEC msdb.dbo.sp_send_dbmail @profile_name = '<database_mail_profile_name>' , @recipients = '<your_email_address>' , @subject = @subject , @body = @msg , @body_format = 'HTML'; END |
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”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
USE [msdb] GO DECLARE @recipients varchar(max) = '<your_email_address'; DECLARE @profile_name sysname = N'<database_mail_profile_name'; DECLARE @max_log_backup_age_in_minutes int = 60; DECLARE @cmd nvarchar(max) = N'DECLARE @msg nvarchar(max); SET @msg = N''''; ;WITH src AS ( SELECT bs.database_name FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE bs.type = ''L'' --only log backups AND bs.backup_finish_date > DATEADD(MINUTE, 0 - ' + CONVERT(nvarchar(10), @max_log_backup_age_in_minutes) + N', GETDATE()) ) , last_backup AS ( SELECT bs.database_name , bs.backup_start_date , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC) FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] WHERE bs.type = ''L'' --only log backups AND bs.backup_finish_date <= DATEADD(MINUTE, 0 - ' + CONVERT(nvarchar(10), @max_log_backup_age_in_minutes) + N', GETDATE()) ) SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE N'' </td></tr><tr><td style="padding-left:10px;">'' END + d.name + N'' </td><td style="padding-left:20px;">'' + COALESCE(CONVERT(nvarchar(30), lb.backup_start_date, 120), '''') FROM sys.databases d LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1 WHERE d.state_desc = N''ONLINE'' AND d.recovery_model_desc <> N''SIMPLE'' --simple recovery affords no log backups AND d.name <> CONVERT(sysname, N''model'') AND NOT EXISTS ( SELECT 1 FROM src WHERE src.database_name = d.name ) ORDER BY d.name; IF @msg <> N'''' BEGIN SET @msg = N''<!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Log Backup Notification from '' + CONVERT(nvarchar(128), @@SERVERNAME) + ''</title> <style> body { font-family: Segoe UI,Verdana,Tahoma; font-size: 10pt; } thead tr td { background-color: #948d8d; padding: 2px; border: solid; border-color: black; border-width: 2px; margin: 0px; text-align:center; } tbody tr td { background-color: #eeeeee; padding-left: 5px; padding-right: 5px; padding-bottom: 2px; border-width: 0px; margin: 0px; } tbody tr { padding-bottom: 0px; padding-top: 0px; } </style> </head> <body> The following databases haven''''t had a log backup during the last ' + CONVERT(nvarchar(10), @max_log_backup_age_in_minutes) + N' minutes:</p><br /> <table><thead><tr><td style="width: 350px;"><p>Database</p></td><td style="width: 150px;"><p>Last Log Backup</p></td></tr></thead><tbody><tr><td style="padding-left:10px;"><p>'' + @msg + N''</p></td></tr></tbody></table> <p>Only databases in FULL or BULK LOGGED recovery model are listed in this email. Please ensure the appropriate transaction log backup job is scheduled for the listed databases, or investigate changing their recovery model to simple. Only change the recovery model to simple if this is a non-critical, non-production database. Simple recovery model prevents point-in-time recovery, and may make our recovery-point-objective impossible to meet. <p style="font-size:9pt;">This email was sent from the [Log Backup Notifier] SQL Server Agent Job on ' + CONVERT(nvarchar(128), @@SERVERNAME) + N'. </body> </html>''; DECLARE @subject nvarchar(1000); SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME; <pre><code>EXEC msdb.dbo.sp_send_dbmail @profile_name = ''' + @profile_name + N''' , @recipients = ''' + @recipients + N''' , @subject = @subject , @body = @msg , @body_format = ''HTML''; |
END
‘;
DECLARE @ReturnCode int;
SELECT @ReturnCode = 0;
BEGIN TRANSACTION;
BEGIN TRY
IF NOT EXISTS (
SELECT sc.name
FROM msdb.dbo.syscategories sc
WHERE sc.name = N’Log Notifications’ AND category_class=1
)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N’JOB’
, @type = N’LOCAL’
, @name = N’Log Notifications’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N’An error occurred adding the Job Category’, 1;
END
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
DECLARE @jobId binary(16); IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs sj WHERE sj.name = CONVERT(sysname, N'Log Backup Notifier') ) BEGIN EXEC msdb.dbo.sp_delete_job @job_name = N'Log Backup Notifier' , @delete_history = 0 , @delete_unused_schedule = 0; END EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Log Backup Notifier' , @enabled = 1 , @notify_level_eventlog = 3 , @notify_level_email = 2 , @notify_level_netsend = 0 , @notify_level_page = 0 , @delete_level = 0 , @description = N'No description available.' , @category_name = N'Log Notifications' , @owner_login_name = N'sa' , @notify_email_operator_name = N'DBA' , @job_id = @jobId OUTPUT; IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred adding the Job', 1; DECLARE @step_name sysname = N'Email DBAs if no log backup has occurred in ' + CONVERT(nvarchar(10), @max_log_backup_age_in_minutes) + N' minutes.'; EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId , @step_name = @step_name , @step_id = 1 , @cmdexec_success_code = 0 , @on_success_action = 1 , @on_success_step_id = 0 , @on_fail_action = 2 , @on_fail_step_id = 0 , @retry_attempts = 0 , @retry_interval = 0 , @os_run_priority=0, @subsystem = N'TSQL' , @command = @cmd , @database_name = N'msdb' , @flags = 0; IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurrred adding the Job Step', 1; EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId , @start_step_id = 1; IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job starting step.', 1; EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId , @server_name = N'(local)'; IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job server to (local)', 1; IF EXISTS ( SELECT 1 FROM msdb.dbo.sysschedules ss WHERE ss.name = N'Log Notification Schedule' ) BEGIN EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Log Notification Schedule'; END DECLARE @start_date int; SET @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112)); EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId , @name = N'Log Notification Schedule' , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 8 , @freq_subday_interval = 1 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_date = @start_date , @active_end_date = 99991231 , @active_start_time = 60000 , @active_end_time = 215959; COMMIT TRANSACTION |
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION;
THROW
END
END CATCH
GO
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