Ola Hallengren – Database Maintenance Scripts – Transaction Log Backup not occuring

Background

Made the change discussed here.

Again, the change was to temporary alter our Recovery Mode to SIMPLE and back. In between, shrunk the transaction log files.

 

Issue

Ran the Ola Hallengren’s transaction Log backup step, but nothing is being backed up.

BTW, the Scripts are available here.

 

Trouble Shooting

Let us review the scripts and see where we are failing.

Stored Procedure – dbo.DatabaseBackup

sys.database_recovery_status – Get last_log_backup_lsn

Check sys.database_recovery_status and get last_log_backup_lsn


    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
    BEGIN
      SELECT @CurrentLogLSN = last_log_backup_lsn
      FROM sys.database_recovery_status
      WHERE database_id = @CurrentDatabaseID
    END

Backup Conditions

skipwhenlogbackupandrecoveryissimpleorcurrentloglsn

Explanation

  1. Skip when LogBackup
    • Recovery Is Simple
    • Or CurrentLogLSN is null

 

sys.database_recovery_status

Script


SELECT 
		  tblSDRS.[database_id] 
		, [databaseName] = db_name(tblSDRS.[database_id])
		, tblSDRS.last_log_backup_lsn

FROM sys.database_recovery_status tblSDRS


Output

sys-database_recovery_status

 

Database Backup – Directly

If we try to backup the database directly by issuing our own “backup log <database-name>” statement. i.e.

Code


BACKUP LOG [eiadev] 
TO  DISK = N'Z:\Backups\eiadev_20161027_1208PM.bak' WITH NOFORMAT, NOINIT
,  NAME = N'enr_Log Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Output

GUI

nocurrentbackup

Textual

Msg 4214, Level 16, State 1, Line 7
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 7
BACKUP LOG is terminating abnormally.

Remediation

Take full backups right away or wait till our next scheduled full backup.

Once that occurs, our transaction backups will run to successful completion.

Summary

So basically because we changed the Recovery Mode from FULL to SIMPLE ( and now back to FULL), we can not issue a transaction log backup statement until we take a FULL Backup.

To avoid that error, Olla’s script proactively checks the sys.database_recovery_status dmv.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s