Post reply

System Database

  • December 27, 2011 at 4:26 am

    #262222

    what is the recommended recovery model for system database?

  • December 27, 2011 at 4:59 am

    #1425639

    The default recovery model is FULL but I (& many prefer) it in Simple recovery model for system databases except Model Database. SQL server use Model database as template for new database so it should be in FULL recovery model.

  • December 27, 2011 at 5:02 am

    #1425640

    Master defaults to simple and, even if set to full still behaves like simple (can't take log backups), so leave that as is.

    TempDB is simple and cannot be changed to full (and can't be backed up).

    Model defaults to full, set it to whatever you want future databases to be in when they are created.

    MSDB defaults to simple, if your jobs and backup history are so sensitive that you need point-in-time restores for it, set it to full and set up the required log backups, otherwise leave it as-is and just take full backups.

    ResouceDB is irrelevant, it's a hidden DB and can't be backed up.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 27, 2011 at 5:04 am

    #1425641

    thanks dev.

  • December 27, 2011 at 5:08 am

    #1425643

    but model database is template of creating new database, so why we set to full? it can be set to simple also.

  • December 27, 2011 at 5:12 am

    #1425644

    Dhirju (12/27/2011)

    but model database is template of creating new database, so why we set to full? it can be set to simple also.

    As I said in my post, it should be set to whatever you want newly created databases to be, so if you want new databases to be in full recovwery, set model to full, if you want new databases to be in simple recovery, set it to simple. If you don't care (you know that you will always check and change recovery models of newly created databases), set it to simple (its log can grow a bit in full recovery due to backups)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 27, 2011 at 5:14 am

    #1425645

    okies okies .

    Now i got it. 😉

    Thanks ..... Gila

Viewing 7 posts - 1 through 6 (of 6 total)

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