0

I am reading this book from Microsoft - it says: "A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed"

Is this correct? I know simple recovery model doesn't support transaction log backup. But think I have read somewhere that full and also bulk recovery model supports transaction log backup.

Improve this question
1
  • please read my answer completely and if possible please clear what you are asking. The author I still believe is not incorrect but we need to look under what context he is talking.
    – Shanky
    Oct 15, 2018 at 10:27

2 Answers 2

Reset to default
3

No, this is not correct.

See SQL Server Recovery Models

If you are utilising the BULK recovery model, this will function the same as the FULL recovery model apart from specific minimally logged operations (SELECT INTO, BULK INSERT, CREATE INDEX...)

If you do not set up log backups, you risk your log files filling.

Improve this answer
9
  • Your answer is incorrect, I guess you misread the question
    – Shanky
    Oct 15, 2018 at 9:56
  • 1
    @Shanky how so? Log backups can also be taken in BULK recovery model.
    – George.Palacios
    Oct 15, 2018 at 9:57
  • Yes correct but the OP is asking about specific piece of information he is reading from the book. Please read the complete line it says for a log backup to be taken in full recovery mode a full backup has to be taken to start the log chain otherwise log backup would fail
    – Shanky
    Oct 15, 2018 at 10:01
  • 1
    @Shanky I have read the full line and I disagree with you - the source literally says "A log backup cannot be taken..." without referring to this situation. I think it is worded badly personally - a log backup CAN be taken. The FIRST log backup cannot.
    – George.Palacios
    Oct 15, 2018 at 10:03
  • Ok so if a database is created in full recovery mode and no full backup has been taken can a log backup be taken ? Or recovery model change from simple to full and no full backup has been taken after changing recovery model, can a log backup be taken the answer is NO. I believe this is what OP is referring
    – Shanky
    Oct 15, 2018 at 10:10
2

A log backup cannot be performed unless the database is in full recovery model and an initial full backup has been performed

Strictly taking into account what author is taking the statement is correct, what it means that if a database is in full recovery model and NO full backup has been done from the time when database was created or when database recovery model was changed to full recovery, you cannot take log backup it would just fail. This is because there was no full backup taken to start the transaction log chain after full recovery model change or when database was created. Such databases are called to be in "Pseudo Simple Recovery Model". A database in full recovery model actually behaves like simple unless a full backup is taken. Paul Randal from SQLSkills.com has more details In this Blog

It is also worth noting that after switching from the bulk-logged recovery model back to the full recovery model, you can backup the transaction log.

For example Let us create a database in full recovery and take log backup it would fail see the screen

enter image description here

Now let us take full backup, change recovery model to simple and again back to full, and take log backup, it will fail again

enter image description here

I believe this is what the author is talking. If full backup has been taken to initiate chain you can very well take transaction log backup in full and bulk logged recovery model.

Now do the same for bulk logged, create a new database in full recovery mode perform some transaction and change recovery to bulk_logged and then back to full recovery. Now perform the log backup it will fail saying

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

But think I have read somewhere that full and also bulk recovery model supports transaction log backup

Yes correct subject to condition that after changing to full recovery or creating the database atleast one full backup has been taken to start the log chain

Improve this answer
4
  • A database could be in BULK recovery model and have had an initial backup taken in FULL mode - perhaps the original source is just worded badly.
    – George.Palacios
    Oct 15, 2018 at 9:57
  • I would not say badly worded its just under what reference the author is talking. I believe he was talking about "Pseudo simple recovery" and that is what OP pointed out and that is what I was answering
    – Shanky
    Oct 15, 2018 at 10:05
  • @Shanky the above is not true - it seems point in time recovery is not at all supported by bulk recovery model. Let me know if you think otherwise. Any reference will be helpful.. It seems that there should also be no bulk logged transactions in the backup fromw hich we are trying to do the point in time restore.
    – variable
    Oct 16, 2018 at 8:58
  • First of all you are asking question which is not relevant to the original question asked so I would not go into deep. Please raise different question I will answer and FYI PIT is completely possible in BL recovery model
    – Shanky
    Oct 16, 2018 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.