The Model Database

  • Comments posted to this topic are about the item The Model Database

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The following operations cannot be performed on the model database:

    ...

    Setting the primary filegroup to READ_ONLY.

    Damn you MSDN!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/17/2014)


    The following operations cannot be performed on the model database:

    ...

    Setting the primary filegroup to READ_ONLY.

    Damn you MSDN!

    +1!!!!!!


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • I also got it wrong after reading MSDN :hehe:.

  • Koen Verbeeck (2/17/2014)


    The following operations cannot be performed on the model database:

    ...

    Setting the primary filegroup to READ_ONLY.

    Damn you MSDN!

    + 1

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Hany Helmy (2/18/2014)


    Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".

    That must be useful. Empty databases on read-only mode.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Koen Verbeeck (2/18/2014)


    Hany Helmy (2/18/2014)


    Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".

    That must be useful. Empty databases on read-only mode.

    Very useful - a database guaranteed to contain no corrupt data :hehe: as long as no-one with enough privileges changes its state.

    Tom

  • Hold on a second here. As far as I'm aware, the model database is used to create tempdb--so if model is actually read-only, wouldn't that then make tempdb read-only, and thus cause some really interesting problems? (Oh, and I also got it wrong due to reading the thing in MSDN...).

  • Even if you put the model db in read-only mode, any new databases you create will NOT inherit that property.

    I assume this holds true for tempdb but I haven't tested it.

    **EDIT** My assumption seems to be correct - after server restart, model remained read-only but tempdb was read-write.

    -----
    JL

  • BWFC (2/18/2014)


    Koen Verbeeck (2/17/2014)


    The following operations cannot be performed on the model database:

    ...

    Setting the primary filegroup to READ_ONLY.

    Damn you MSDN!

    +1!!!!!!

    +1. MSDN is wrong again. I thought I remembered reading something about this, but it's a ways back in the memory banks, so I looked it up. Since I was wrong, I just had to try it and it is allowed.

  • Nice information,but how do you define "newly created"?

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • paul.knibbs (2/18/2014)


    Hold on a second here. As far as I'm aware, the model database is used to create tempdb--so if model is actually read-only, wouldn't that then make tempdb read-only, and thus cause some really interesting problems? (Oh, and I also got it wrong due to reading the thing in MSDN...).

    a new database only inherits ALL the properties of model if you use the GUI to create it and take the defaults. Creating a new database via TSQL with no parameters only the initial size of the .mdf file is inherited from model.

    ---------------------------------------------------------------------

Viewing 15 posts - 1 through 15 (of 28 total)

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