Home > Backup & Recovery, Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Msg 4208 / Msg 4214 / Msg 3013 – BACKUP LOG is terminating abnormally – Backup & Recovery

Msg 4208 / Msg 4214 / Msg 3013 – BACKUP LOG is terminating abnormally – Backup & Recovery

Recently, I encountered this message in one of our production servers. When I checked the SQL Agent Log I found below error message.

BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.

I searched for a solution and found a KB article which describes the issue, KB928317.

The KB article describes only one scenario that a Full backup must exist before a Log backup can be taken. Which is depicted below:

– © 2011 – Vishal (http://SqlAndMe.com)
CREATE DATABASE [TestDB]

ALTER DATABASE [TestDB] SET RECOVERY FULL

BACKUP
LOG [TestDB] TO
DISK = N'C:\TestDBLog.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The BACKUP LOG statement will throw and error since there is no Full backup:

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

 

To resolve this error, you must take a full backup before you execute the BACKUP LOG statement. The KB article did not seem to apply to my situation as a Full backup of our production database is taken daily.

The root cause in my situation was the recovery model of the database, which was set to SIMPLE !

Let’s try this on our TestDB

– © 2011 – Vishal (http://SqlAndMe.com)
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

BACKUP LOG [TestDB] TO
DISK = N'C:\TestDBLog.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

The error message you receive is self-explanatory:
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use
BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

 

But, if the statement is executed by a SQL Job, you will only receive below in error message:
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

 

This can lead to confusion in identifying correct issue.

 

When I investigated further to find out why recovery model was set to SIMPLE, I found that a Re-indexing Job was scheduled which set the recovery model to SIMPLE to avoid Log file growth due to indexing operations. The Job will change the recovery model to SIMPLE, execute for around 14 hours and then it will set the recovery model to FULL.

 

A better approach is to perform indexing operations under BULK-LOGGED recovery model, since CREATE/ALTER and DROP INDEX operations are minimally logged under this model.

 

BOL : Backup Under the Bulk-Logged Recovery Model

 

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Srinath
    February 10, 2012 at 7:08 pm

    Thanks a lot,,This is what I have been searching for…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

Follow

Get every new post delivered to your Inbox.

Join 268 other followers

%d bloggers like this: