Jul 202010
 

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:

I've never actually seen "The Matrix"

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:

I hope you find this helpful!

  6 Responses to “Switching Between Recovery Models”

  1. I’d say you have the start of one of those huge SQL Server posters like the ones that Quest gives out!

  2. Clear, Concise and drawn out. Excellent post on all accounts, sir. I’ll be referencing this in the future when helping folks out of this confusion, I’m sure of it.

    • Thanks so much for your kind words! I hope it can help some people out and maybe make things a little less confusing, too.

  3. I am a sucker for posts written like this. This is one way I learn actually. I had a boss before who referred to me as the girl who thinks in binary.;) For example, when defining logic for three (in program flow specs), I’d start listing 000, 001, 010, etc. Then, I’d specify what happens for each combo (e.g. raise error, display this msg, display this warning, go to next step, etc).

    Really good job, Bob. :)

    • That’s how I learn as well. Whenever I can find a way to make something into a chart, I find it much easier to comprehend. As for logic problems, Karnaugh maps have always been my friend. Glad you enjoyed it!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)