February 18, 2014 at 6:04 am
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
February 18, 2014 at 6:06 am
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
February 18, 2014 at 6:12 am
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
February 18, 2014 at 6:45 am
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
February 18, 2014 at 7:15 am
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.
February 18, 2014 at 7:15 am
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. 🙂
---------------------------------------------------------------------
February 18, 2014 at 7:48 am
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
February 18, 2014 at 8:03 am
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
February 18, 2014 at 9:10 am
It is interesting to see how much discussion a simple and starightforward QotD can provoke... Thanks, free mascot!
February 18, 2014 at 10:27 am
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!
February 18, 2014 at 10:06 pm
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."
February 20, 2014 at 10:33 am
February 27, 2014 at 12:02 am
Easy question. 🙂
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply