Full or simple recovery for system DBs?

  • 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.

  • 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

  • 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

  • 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

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

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