Switching Between Recovery Models

I've seen many articles and blog posts concerning what to do when you have to switch between SQL Server Recovery Models.  A lot of these tips are very important, as they can mean the difference between being able to recover your data and the much less desirable opposite.

After a lot of web searches on the subject I came to realize there's much material for individual cases, but no real "one stop shop" for what to do when switching between any given recovery model.  Thus, my recovery model chart was born.  Since a diagram is worth almost as many words as a picture, I'll show it to you and explain a little bit afterwards:

As you can see, you pick the recovery model you're currently in along the left side and the one you wish to switch to along the top.  It will tell you what you should do both before and after the switch.

You'll notice the asterisk in the entries that involve switching from the simple recovery model.  I made it a backup* because depending on the situation a full backup may not be necessary.  If a full backup already exists, a differential backup should suffice for bridging the LSN gap created by switching to the simple recovery model.  Paul Randal covered this in his "DBA Myths" series a few months ago.

Wanting to keep the chart as simple as possible, I opted not to include rationales in the chart itself.  If any of these don't make sense, here they are:

Switching to the simple recovery model: Perform a log backup beforehand to allow recovery to that point.  Switching to the simple recovery model will break the transaction log backup chain (which may be desirable if you're trying to truncate or shrink the log.)  After the switch is made, you'll need to disable any log backup jobs, as log backups aren't possible under the simple recovery model.  Continue backing up with full or differential backups as you (hopefully) were before.

Switching from the simple recovery model: After the switch, perform either a full (or differential) backup to start (or restore) the transaction log backup chain as described above.  Until this backup is performed, the transaction log will continue to be automatically truncated just like in the simple recovery model.  You will also need to create (or enable) transaction log backup jobs.

Switching from full to bulk-logged: Perform an extra log backup prior to the switch to ensure recoverability up to that point in time.  Regularly-scheduled log backups may continue while under the bulk-logged recovery model.

Switching from bulk-logged to full: Perform an extra log backup immediately following the switch to allow point in time recoverability from that point forward.

Further Reading

I compiled all the info for this chart from the following sources:

• MSDN – Considerations for Switching from the Full or Bulk-Logged Recovery Model
• MSDN – Considerations for Switching from the Simple Recovery Model
• Paul Randal – Misconceptions Around the Log and Log Backups

I hope you find this helpful!