Backup/Restore
-
TomThomson (2/21/2014)
The read_only property of the model database has no effect on newly created databases, however created. Whatever the model value is, the value for database created by restore is the value in the backup being restored, for database created by ATTACH it is as specified in the files being attached, and for database created by CREATE DATABASE it is FALSE. The property in model determines only whether model can be written or not.not quite true, if you create a new database via the SSMS GUI (not TSQL), the newly created database would inherit the read_only property of the model database.
---------------------------------------------------------------------
-
george sibbald (2/23/2014)
TomThomson (2/21/2014)
The read_only property of the model database has no effect on newly created databases, however created. Whatever the model value is, the value for database created by restore is the value in the backup being restored, for database created by ATTACH it is as specified in the files being attached, and for database created by CREATE DATABASE it is FALSE. The property in model determines only whether model can be written or not.not quite true, if you create a new database via the SSMS GUI (not TSQL), the newly created database would inherit the read_only property of the model database.
However the question was set by using TSQL. Not only the read_only, but all other properties are inherited. Btw, via SSMS GUI you can change many of the properties. As the question was about restoring and creating a new database, +1 for the Tom's comment.
Igor Micev,My blog: www.igormicev.com
-
Nice and easy thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Igor Micev (2/23/2014)
george sibbald (2/23/2014)
TomThomson (2/21/2014)
The read_only property of the model database has no effect on newly created databases, however created. Whatever the model value is, the value for database created by restore is the value in the backup being restored, for database created by ATTACH it is as specified in the files being attached, and for database created by CREATE DATABASE it is FALSE. The property in model determines only whether model can be written or not.not quite true, if you create a new database via the SSMS GUI (not TSQL), the newly created database would inherit the read_only property of the model database.
However the question was set by using TSQL. Not only the read_only, but all other properties are inherited. Btw, via SSMS GUI you can change many of the properties. As the question was about restoring and creating a new database, +1 for the Tom's comment.
However, as often happens, the discussion on the question had wandered onto related subjects such as create and attach, and Tom had also done that, so it was relevant to point out there is one way the read_only property of the model database can effect newly created databases. I didn't mention the other properties as the question was about read_only in particular.
BTW I never said the question was incorrect.
---------------------------------------------------------------------
-
nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Not every property is retained though... Service Broker Enabled will always be false when restoring or attaching a database. Service Broker GUID will be retained though.
-
nice question..
-
george sibbald (2/23/2014)
TomThomson (2/21/2014)
The read_only property of the model database has no effect on newly created databases, however created. Whatever the model value is, the value for database created by restore is the value in the backup being restored, for database created by ATTACH it is as specified in the files being attached, and for database created by CREATE DATABASE it is FALSE. The property in model determines only whether model can be written or not.not quite true, if you create a new database via the SSMS GUI (not TSQL), the newly created database would inherit the read_only property of the model database.
Only if you choose to inherit that - using the SSMS GUI allows you to choose whether the database is created RO or not, so you don't inherit the model db value for that if you don't want to. With a big fat GUI like that surely the norm is to look at all the options and check that you are getting what you want, rather than just accept whatever comes up? And certainly for me different databases have different properties, so I tend to look at everything when (extremely rarely) I use that SSMS GUI to create a DB.
Tom
-
Tom,
I only brought this option up as your post contained the phrase 'however created'. The thing is it is the DEFAULT behaviour of the GUI to inherit the model database properties, whereas in TSQL if you just issue create database dbname with no parameters it is the default behaviour not to inherit them, the exact opposite. The GUI is more likely to be used by the accidental or less experienced DBA, and they may not be aware of, or expecting, this behaviour. They are certainly more likely to accept defaults.
I think it is worth being aware that issuing a basic create statement, and using the GUI and just entering a database name might not give you the same result.
george
---------------------------------------------------------------------
-
george sibbald (2/25/2014)
I think it is worth being aware that issuing a basic create statement, and using the GUI and just entering a database name might not give you the same result.george
I agree 100% with that.
More: I guess my view of all this arises from thinking about it in terms of the language, instead of in terms of what's actually happening, and I'm too ancient to think of the GUI as a language (despite having told people, rather often, that that's how they ought to think of it). :blush:
Tom
-
Good question. 🙂
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply