Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Model DB Expand / Collapse
Author
Message
Posted Wednesday, June 09, 2010 9:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
Comments posted to this topic are about the item Model DB



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935106
Posted Wednesday, June 09, 2010 9:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Great question, thanks! Though it would be nice for a little explanation behind the results.

Considering that BOL states:

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

•Setting the database or primary filegroup to READ_ONLY."

Which obviously isn't true, you can set it, it just doesn't propagate it to new DBs.

Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA. (That is what gave it away for me.)
Post #935107
Posted Wednesday, June 09, 2010 10:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
Thanks. True - BOL does state that the MODEL DB cannot be set to ReadOnly, that's why I found this to be an interesting behavior.

Thanks for pointing out the DB name too.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935118
Posted Wednesday, June 09, 2010 10:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 3:59 AM
Points: 1,938, Visits: 1,149
hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.
but it shows the model database as read only.


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #935119
Posted Wednesday, June 09, 2010 10:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
malleswarareddy_m (6/9/2010)
hi without the script i set the model database into read only.then i create the new database but i am not able to create the new table because it is read only it gives an error as one of the option mentioned.but with the script i am able to create new table.
but it shows the model database as read only.


When you attempt to create a table, make sure you have switched context to the new database.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935121
Posted Wednesday, June 09, 2010 10:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
UMG Developer (6/9/2010)

Though the first option for script 4 should be updated to say NewReadOnlyDB instead of TestA.


Steve, could you update that option? It is the answer option with the error message.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935122
Posted Wednesday, June 09, 2010 10:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 3:59 AM
Points: 1,938, Visits: 1,149
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)
then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #935133
Posted Wednesday, June 09, 2010 10:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
malleswarareddy_m (6/9/2010)
I put it newreadonly but still it allows to create a table. which is not possible with setting with tool. (database properties----readonly---true.)
then create new db with wizard. It automatically creates new database with Readonly.
Here with the script newreadonly database will not create with read only.It create database as normal database.It does not inherit the properties from model.


Interesting.

If I understand correctly, you:
1) Set the Model DB to readonly
2) Use the wizard to create a new database
3) Switch context to the new database
4) execute a create table statement - which fails

What version and SP of SQL server? When I do those steps I see the same results as the script (sql 2005 and 2008).




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #935136
Posted Thursday, June 10, 2010 1:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 3,683, Visits: 4,818
Good question.
I found the inconsistency with BOL interesting.
Thanks


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #935202
Posted Thursday, June 10, 2010 2:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 09, 2013 11:12 PM
Points: 1,263, Visits: 1,081
Just verified the finding in SQL Server 2005:

- With MODEL being in READ_ONLY mode, when creating a database using the wizard, it is created read_only. No more changes are possible.

- With MODEL still in READ_ONLY, when creating a database via a SQL script, it is created in read/write mode and tables etc. can be added.

Very interesting behavior--especially given the fact that BOL states one cannot set the MODEL database to READ_ONLY (that's why I did not get my point).
Post #935217
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse