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)