Full Recovery without Log Backups? Get Notified!

2019-03-07 · by Hannah Vernon · in recovery

Why you need log backups in Full Recovery Model

Databases in the Full Recovery Model¹ log every transaction into the transaction log file. This enables point-in-time recovery², 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:

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;"><p>' + 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>
    <p>The following databases haven''t had a log backup during the last 120 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 YELLER\ONGOLE.</p>
    </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

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;"><p>' + 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>

<head>

<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>

<p>The following databases haven''t had a log backup during the last 120 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 YELLER\ONGOLE.</p>

</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”.

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''</p></td></tr><tr><td style="padding-left:10px;">'' END 
    + d.name + N''</p></td><td style="padding-left:20px;"><p>'' + 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>
    <p>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'.</p>
    </body>
    </html>'';
    DECLARE @subject nvarchar(1000);
    SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

    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

    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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

USE [msdb]

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''</p></td></tr><tr><td style="padding-left:10px;">'' END

+ d.name + N''</p></td><td style="padding-left:20px;"><p>'' + 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>

<head>

<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>

<p>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 />

<p style="font-size:9pt;">This email was sent from the [Log Backup Notifier] SQL Server Agent Job on ' + CONVERT(nvarchar(128), @@SERVERNAME) + N'.</p>

</body>

</html>'';

DECLARE @subject nvarchar(1000);

SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

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

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

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

Full Recovery without Log Backups? Get Notified!

Why you need log backups in Full Recovery Model

The Script

The Even Better Script

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta