February 17, 2014 at 8:12 pm
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."
February 17, 2014 at 8:21 pm
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
February 17, 2014 at 11:58 pm
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
February 18, 2014 at 12:46 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!
+1!!!!!!
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 18, 2014 at 1:16 am
I also got it wrong after reading MSDN :hehe:.
February 18, 2014 at 1:20 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!
+ 1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
February 18, 2014 at 1:24 am
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
February 18, 2014 at 1:28 am
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
February 18, 2014 at 3:36 am
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
February 18, 2014 at 4:27 am
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...).
February 18, 2014 at 4:55 am
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
February 18, 2014 at 5:19 am
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.
February 18, 2014 at 5:44 am
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
February 18, 2014 at 6:02 am
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.
---------------------------------------------------------------------
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy