Full or simple recovery for system DBs?

Question

Full or simple recovery for system DBs?

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
January 29, 2018 at 12:27 pm

#332962

I am aware how important system DBs are and how important FULL recovery mode is but do system databases have to have FULL recovery mode enabled or is it ok for me to set it to Simple. I am not aware of the best practices when it comes to choosing the recovery model for system databases.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

NewBornDBA2017 - Monday, January 29, 2018 12:27 PM
I am aware how important system DBs are and how important FULL recovery mode is but do system databases have to have FULL recovery mode enabled or is it ok for me to set it to Simple. I am not aware of the best practices when it comes to choosing the recovery model for system databases.

This article explains the different recovery models supported, the defaults and options for the system databases:
Recovery Models for System Databases

Sue

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

It depends on how much far back you want to go. I don't think I've ever done a point-in-time restore to the any system database so, for me, simple is the way to go for system databases. The exception is the model DB; every time you create a new database you are basically making a copy of your model DB. On production boxes with databases that need point-in-time backups I leave the model DB default as Full. On Dev boxes where I almost never do point-in-time restores, I always change the model DB to simple.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Alan.B - Monday, January 29, 2018 12:45 PM
It depends on how much far back you want to go. I don't think I've ever done a point-in-time restore to the any system database so, for me, simple is the way to go for system databases. The exception is the model DB; every time you create a new database you are basically making a copy of your model DB. On production boxes with databases that need point-in-time backups I leave the model DB default as Full. On Dev boxes where I almost never do point-in-time restores, I always change the model DB to simple.

^^^ This