The Model Database

Question

The Model Database

Viewing 14 posts - 16 through 28 (of 28 total)

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

James Lean SSCertifiable Points: 5757 More actions · Answer 1

Actually, MSDN is NOT wrong. You cannot make the primary filegroup read-only. This is actually a restriction on ANY database.

You can make the whole database read-only however.

So, this works:

ALTER DATABASE model

SET READ_ONLY

But this doesn't work:

ALTER DATABASE model

MODIFY FILEGROUP [PRIMARY] READ_ONLY

And, as I said above, even if you make model read-only, new databases will be created read-write.

-----
JL

James Lean SSCertifiable Points: 5757 More actions · Answer 2

george sibbald (2/18/2014)
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.

Good catch about difference between T-SQL and GUI 🙂

-----
JL

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 3

george sibbald (2/18/2014)
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.

Nice insight.

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

Isn't this a repeat of the question Steve posted 02/22/2012?? (Or at least the first part of Steve's question.)

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Thomas Abraham (2/18/2014)
Isn't this a repeat of the question Steve posted 02/22/2012?? (Or at least the first part of Steve's question.)

Yes.

The discussion is a bit less interesting this time, though.

Tom

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

James Lean (2/18/2014)
Actually, MSDN is NOT wrong. You cannot make the primary filegroup read-only. This is actually a restriction on ANY database.
You can make the whole database read-only however.

I agree, on what you said, however, one remark here. In the MSDN articles for the previous SQL versions, it was incorrect

Setting the database or primary filegroup to READ_ONLY.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

TomThomson (2/18/2014)
Thomas Abraham (2/18/2014)
Isn't this a repeat of the question Steve posted 02/22/2012?? (Or at least the first part of Steve's question.)
Yes.
The discussion is a bit less interesting this time, though.

oh, thanks. 🙂

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

TomThomson (2/18/2014)
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.

Very useful in those cases where users or developers have enough rights to create a database. Helps to slow down data growth coming from these unknown databases. That is until they figure out you have pulled one over on them.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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!

Yeah that can be a bit misleading. The primary filegroup can't be set read_only directly. But the database can be set read_only.

It would be more fun if the primary filegroup could be set read_only directly.

Here's a quick script for fun...

ALTER DATABASE model

SET READ_ONLY WITH ROLLBACK IMMEDIATE

SELECT name,state_desc,is_read_only

FROM sys.databases

WHERE name = 'model';

/* yup the database is read_only */

ALTER DATABASE model

MODIFY FILEGROUP [PRIMARY] READ_ONLY;

GO

/* that should result in an error

Msg 5047, Level 16, State 1, Line 1

Cannot change the READONLY property of the PRIMARY filegroup.

*/

USE model;

GO

SELECT *

FROM sys.filegroups;

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

Revenant SSC-Forever Points: 42467 More actions · Answer 10

It is interesting to see how much discussion a simple and starightforward QotD can provoke... Thanks, free mascot!

Miles Neale SSChampion Points: 13147 More actions · Answer 11

Could complain won't. MSDN shafted me as well.

Just goes to prove you cannot believe everything Microsoft prints. I wonder what percent of them would get this? 🙂

M.

Not all gray hairs are Dinosaurs!

free_mascot One Orange Chip Points: 27169 More actions · Answer 12

Thank you all; for participating in the discussion.

Glad to see lots of discussion around. However didn't notice that this question was asked earlier. Thank you for pointing out.

Cheers!

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

Primo Dang SSCrazy Points: 2643 More actions · Answer 13

Here's the link to the previous question[/url], for anyone who got curious like me but doesn't want to go through the QotD pages. 😛

sqlnaive SSCoach Points: 17437 More actions · Answer 14

sqlnaive

SSCoach

Points: 17437

February 27, 2014 at 12:02 am

#1692547

Easy question. 🙂