February 22, 2012 at 1:58 am
BrainDonor (2/22/2012)
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.
BrainDonor, can you intercept commands by the profiler? ... post it, please! 🙂
February 22, 2012 at 2:03 am
BrainDonor (2/22/2012)
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.
Checked and confirmed that it works the same here.
What apparently happens is that SSMS gets all properties from the model database and uses them to pre-populate the properties in the Create Database dialog. You can then choose to change them or leave them untouched. SSMS does not check if you changed anything; it simply scripts a whole bunch of ALTER DATABASE statements for all options. Including the read_only option.
PS: No need for Profiler, Carlo - you can simply use the Script options in the create database window.
February 22, 2012 at 2:20 am
Hugo Kornelis (2/22/2012)
Checked and confirmed that it works the same here.What apparently happens is that SSMS gets all properties from the model database and uses them to pre-populate the properties in the Create Database dialog. You can then choose to change them or leave them untouched. SSMS does not check if you changed anything; it simply scripts a whole bunch of ALTER DATABASE statements for all options. Including the read_only option.
PS: No need for Profiler, Carlo - you can simply use the Script options in the create database window.
Thank you, Hugo. 😛
That's why I prefer use t-sql statements directly to create/alter database/table/sp/func/etc.
GUI uses options or commands that you do not expect.
February 22, 2012 at 2:45 am
Hi !
Correct answer is Yes and NO
Tested on
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
1. Set db model to read_only mode
use model
alter database model set read_only
select DATABASEPROPERTY('model','isReadONLY')
works! model has read_only property set to 1
2. Create new db and check its status
create database tmp
select DATABASEPROPERTY('tmp','isReadONLY')
and we will see that new database does not Inherit property read_only
😀
GIVE ME MY POINT BACK !!!
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2
February 22, 2012 at 2:45 am
Good question and as others have already pointed out, the documentation is wrong:
From http://msdn.microsoft.com/en-us/library/ms186388.aspx
The following operations cannot be performed on the model database:
Adding files or filegroups.
Changing collation. The default collation is the server collation.
Changing the database owner. model is owned by dbo.
Dropping the database.
Dropping the guest user from the database.
Enabling change data capture.
Participating in database mirroring.
Removing the primary filegroup, primary data file, or log file.
Renaming the database or primary filegroup.
Setting the database to OFFLINE.
Setting the database or primary filegroup to READ_ONLY.
Since I'm still running 2005, I relied on BOL and it failed me this time.
February 22, 2012 at 3:18 am
Hi,
If you read the Restrictions on this link
http://msdn.microsoft.com/en-us/library/ms186388.aspx
you'll find that the model database cannot be set to read_only.
But, okey,
I should have tried it!, it is my mistake that i didn't try.
-Igor
Igor Micev,My blog: www.igormicev.com
February 22, 2012 at 3:18 am
Hi,
If you read the Restrictions on this link
http://msdn.microsoft.com/en-us/library/ms186388.aspx
you'll find that the model database cannot be set to read_only.
But, okey,
I should have tried it!, it is my mistake that i didn't try.
-Igor
Igor Micev,My blog: www.igormicev.com
February 22, 2012 at 3:18 am
Hi,
If you read the Restrictions on this link
http://msdn.microsoft.com/en-us/library/ms186388.aspx
you'll find that the model database cannot be set to read_only.
But, okey,
I should have tried it!, it is my mistake that i didn't try.
-Igor
Igor Micev,My blog: www.igormicev.com
February 22, 2012 at 3:40 am
Interesting question, and based on the comments here, I take it there is (and still for me) a lot of confusion.
If you rely on Books Online / MSDN and the documentation, I would take it the answer should be NO and NO (since the only No answer:-))
If you rely on scripting manually, I guess the answer is Yes and No as is proved by all the scripts
If you use UI in SSMS the answer becomes Yes / Yes (the supposed correct answer)
I would say this is still wrong as the UI actually just does a bunch of Alter Database statements, and the actual create statement is set up with a NON-READONLY database. You could then say that a DB is not created at all because I issue a DROP DB statement after that!
If this truly was the case, the TempDB would never end up working on that server since it is recreated from Model each time!!!
I guess the answer really should be it depends! and the lesson (or in this case points) should be in the form of knowledge. I have definitely learnt something today, which I guess is the point of QOTD! Thanks
February 22, 2012 at 4:04 am
I also picked Yes and No, because the question about MODEL being read-only has come up before, so I knew it was possible to set it to read-only; but it was obvious that new databases couldn't inherit that property, because then tempdb wouldn't be all that useful...
February 22, 2012 at 4:56 am
Is there a correct answer at all :hehe:
In theory you can't set or maybe you can, depends on which article you read.
In practice you can set and new database gets option or not, depends on method.
:w00t: All of the above
February 22, 2012 at 4:57 am
well, I wrong!! I tested in SQL Server 2k8 10.0.1600.22 (x64) Developer Edition and occurs same result!!
I can alter database model to read only but the new databases not carry the changes!!!
If we considered that documentation is wrong, why set up the model database to read_only if the changes do not take effect in the new database?
I think that the documentation is right!
February 22, 2012 at 5:05 am
rfr.ferrari (2/22/2012)
If we considered that documentation is wrong, why set up the model database to read_only if the changes do not take effect in the new database?
I think that this is in fact very useful. It prevents accidental changes to model (which would propagate to all new databases), and at the same time saves you the hassle of having your new databases start out in read-only mode (which, frankly, would make no sense at all).
I think that the documentation is right!
That leads to the question: which part of the documentation. I didn't follow the links, but I do understand from the discussion here that the documentation contradicts itself.
I prefer to believe that the documentation is incorrect and the observed behaviour is as intended. It's how I would want it to work.
February 22, 2012 at 5:12 am
dawryn (2/22/2012)
Is there a correct answer at all :hehe:In theory you can't set or maybe you can, depends on which article you read.
In practice you can set and new database gets option or not, depends on method.
:w00t: All of the above
+1
Question: if you restart an engine SQL server 2008 R2 with its model set to read-only, would the model stays read-only or it return automatically to its default?
Viewing 15 posts - 16 through 30 (of 62 total)
You must be logged in to reply to this topic. Login to reply