Here is the interesting conversation I recently heard between two teammates in one of the organizations I visited. Their chat ended with Error 4214, and it is a very common one. It says BACKUP LOG cannot be performed because there is no current database backup.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
9 Comments. Leave new
Yes.We can take differential backup instead of the full backup.I tried in SQL2012
Yes you can take Differential backup but We can’t take Differential backup of the database without one full backup first.
Pratham is right !!!
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.
YES you can take a differential backup for a DB in Simple recovery model
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
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).
yes..its working …we can take Transaction log backup by using Differential Backup….
Yes I can take differential backup after successful full backup.