SQL SERVER – Fix – Error: 4214: BACKUP LOG cannot be performed because there is no current database backup.

Here is the interesting conversation I recently heard between two teammates in one of the organizations I visited.

DBA Jr: I can’t take transactional backup of the database.
DBA Sr: What is the recovery model?
DBA Jr: How to find the recovery model?
DBA Sr: Go to SSMS >> Right Click on Your Database >> Select Properties >> Go to Options >> See the Recovery Model:

DBA Jr: It is a Simple recovery model.
DBA Sr: Convert it to Full by Changing the drop down on the recovery model.
DBA Jr: Done! Now?
DBA Sr: Take your transactional backup of the database again.
DBA Jr: Trying…
Well, I get the following error -

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

DBA Sr: Oh, let us search online now.

(I was just happened to hear the conversation. I decided to participate in the conversation.)

Pinal: The reason for the error is that there is no full backup exists. You can’t take transactional backup of the database without one full backup first.
DBA Jr: We had taken the full backup before we change the recovery model.
Pinal: Well, once you change your recovery model from Simple to Full, you should take a full backup before continuing the log backup process.
DBA Sr: Oh, in that case when we change the recovery model from Full to Simple, do we have to take full backup as well for log backup?
Pinal: Well, a few moments ago, you just taught DBA Jr. That for log backup he needs to have a full recovery model. In Simple recovery model log backup is not possible. Remember!
DBA Sr: Oh, I forgot!
DBA Jr: Can I take differential backup instead of the full backup?

I leave the question for all of you for homework. Please create a database and try this out yourself, I will post a valid answer in future blog posts. Please post your answer in the comments section of the blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – Fix – Error: 4214: BACKUP LOG cannot be performed because there is no current database backup.

  1. yes, we can take Differential backup to get the T-log backup but for the differential backup to be initiated we need the Full backup first.So for we need the Full backup as a base point in order to take diffrential/T-log backup.

  2. When we change the recovery model from SIMPLE to FULL, the LSN* chain is NOT created. So theoretically, while we can take LOG backup, realistically database is NOT yet ready for LOG backups.

    So what do we do?
    Take a backup which can ‘Initiate’ the LSN chain

    What types of backups can initiate LSN Chain?
    1. FULL Backup
    2. Differential Backup

    *LSN – Log Sequence Number

  3. Differential backup works!

    I’ve use this technique to repair backup chain after truncating transaction logs (switching recovery model to SIMPLE and them to FULL again).

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