Model Database

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

  • A good question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • A very good question Steve

    M&M

  • select @@VERSION

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Are answer right?

    Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)

    I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database

    I Have Nine Lives You Have One Only
    THINK!

  • handkot (2/21/2012)


    select @@VERSION

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Are answer right?

    Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)

    I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database

    I agree, tested in R2, model can be set to READ_ONLY, but new DB not READ_ONLY.

    Selected "Yes and No" as the answer.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • handkot (2/21/2012)


    select @@VERSION

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Are answer right?

    Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)

    I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database

    You are running SQL 2008(10.0.5500.0) not SQL 2008 R2 (10.5.xxxx.0).. And the answer is correct. Just verified it..

    I can't think of a case where I would ever do this but its good to know..

    CEWII

  • Please see screen shot of the test I did....I'm running R2....

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • A good question to learn something.

    But the new db created are not with read only option.

    Are we missing something here. 🙂

  • Damn you MSDN!

    Any documentation that supports the correct answer?

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

  • Koen Verbeeck (2/22/2012)


    Damn you MSDN!

    Any documentation that supports the correct answer?

    I found this: http://msdn.microsoft.com/en-us/library/ms190249.aspx

    It states that:

    To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.

    In the list of options there is READ_ONLY

  • I absolutely agree on that!

    Elliott Whitlow (2/21/2012)


    handkot (2/21/2012)


    select @@VERSION

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Are answer right?

    Which isn't true, you can set the MODEL database to READ_ONLY, and the property is even set, but it isn't propagated to new databases. (You can set it either via ALTER DATABASE or via SSMS.)

    I set the value READ_ONLY to the MODEL, but the property has been FALSE for new database

    You are running SQL 2008(10.0.5500.0) not SQL 2008 R2 (10.5.xxxx.0).. And the answer is correct. Just verified it..

    I can't think of a case where I would ever do this but its good to know..

    CEWII

  • Based on a vague recollection, I answered that you can set it on the model database, but it won't affect new databases. I then found I apparently was wrong.

    After reading the comments, I decided to check it:

    USE tempdb;

    SELECT @@version;

    go

    SELECT name, is_read_only FROM sys.databases WHERE name = 'model';

    ALTER DATABASE model SET READ_ONLY WITH NO_WAIT;

    SELECT name, is_read_only FROM sys.databases WHERE name = 'model';

    go

    CREATE DATABASE Test_QotD;

    SELECT name, is_read_only FROM sys.databases WHERE name IN ('model', 'Test_QotD');

    DROP DATABASE Test_QotD;

    go

    ALTER DATABASE model SET READ_WRITE WITH NO_WAIT;

    go

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

    Jun 17 2011 00:54:03

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    name is_read_only

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

    model 0

    name is_read_only

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

    model 1

    name is_read_only

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

    model 1

    Test_QotD 0

    (EDIT: Removed white space from query results for readability)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • After the comments, I think its safe to say:

    Yes, model can be set to Read_Only

    No, it wont affect new databases created?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Carlo Romagnano (2/22/2012)


    Koen Verbeeck (2/22/2012)


    Damn you MSDN!

    Any documentation that supports the correct answer?

    I found this: http://msdn.microsoft.com/en-us/library/ms190249.aspx

    It states that:

    To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.

    In the list of options there is READ_ONLY

    Close enough 🙂

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

  • Hugo Kornelis (2/22/2012)


    Based on a vague recollection, I answered that you can set it on the model database, but it won't affect new databases. I then found I apparently was wrong.

    After reading the comments, I decided to check it:

    Running your code I get the same results.

    However, try this through the SSMS GUID. If I change 'Database Read Only' to TRUE from the Properties and then right-click on 'Databases' to create a New Database, it does as given in the answer.

    Two ways to do this with two different results.

    Steve Hall
    Linkedin
    Blog Site

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

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