SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005


SQL Server 2005

Author
Message
henry-f
henry-f
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 59
if the master database has no reference to the "resource" database.
select * from master.sys.sysdatabases

then, mssqlsystemresource.mdf is a file that happens to have a .mdb extension.
Biggles-581128
Biggles-581128
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 208
A database that remains hidden and isn't even reported in the master.sys.sysdatabases has got to be a System Database!! It seems only the system can do anything with it Smile

Nice question, get's you thinking about what is under the hood.
majorbloodnock
majorbloodnock
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1431 Visits: 3062
It's a file that contains data. What is a database if not that?
It's installed as part of an instance, and used by SQL Server. What is a system database if not that?
Microsoft (the people who designed the product) state unequivocally that they regard it as a system database. Who has the right to decide on its classification if not them?
It's not like other SQL databases. So what? How does that make it less of a database so long as the above criteria have been met?

Semper in excretia, sumus solum profundum variat
Arno H. Janssen
Arno H. Janssen
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 52
The Resource Database is a System Database. Extract from SQL Server 2005 Administration (under System Databases): "...It is new to SQL Server 2005 and contains all the read-only critical system tables, metadata, and stored procedures that SQL Server [2005] needs to run... The Resource database cannot be seen through Management Studio... You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource..."
stevehindmarsh
stevehindmarsh
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 584
Quoting from above..
"You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource..."

So, the question should have named the database correctly as MSSQLSystemResource.

We all seem agree there is a hidden 'resource' which may or may not qualify as a database - my point earlier is that the question is vague\incorrect in the name.

Microsoft documentation refers to this as the resource database.

But, if the USE statement needs to have MSSQLSystemResource - then the database name is MSSQLSystemResource. The question is incorrect.

If USE Resource will never work. Then the question is incorrect.



george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10232 Visits: 13687
The question is correct.

I got it wrong because I always refer to it as mssqlsystemresource and thought 'resource' was incorrect shorthand, but turns out microsoft refer to it as the resource database, so I have learnt something and in future will refer to it as 'resource', thus saving myself the effort of saying 'mssqlsystemresource' which does not roll off the tongue.

And this post gets me my point back Smile

---------------------------------------------------------------------
Jeff Deluca
Jeff Deluca
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 229
I can see the debate over this question, however, I'm going to side with this statement:
"The physical file name of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf."

It's my opinion that the question's use of the shorthand name, which microsoft also uses in documentation, is ok.

Either way, I enjoy the community's input...gets me thinking in the morning.

source: http://msdn.microsoft.com/en-us/library/ms190940.aspx


Cheers,

Jeff
stevehindmarsh
stevehindmarsh
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 584
There's an excellent blog by Kalen Delaney on this subject :- http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

I think her query from sysdbreg table does still show some ambiguity in the database name though Smile



WILLIAM MITCHELL
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2106 Visits: 3057
I agree that Resource is a system database with the physical name mssqlsystemresource.mdf, in the sense that the system uses it. But I can also see a physical file named distmdl.mdf that is not mentioned in the msdn article, but apparently is used as a model for the distribution database if you set up replication, so isn't that also a system database?

OTOH if you open SSMS you can only see the four databases that most of us think of when you say 'system database'.

I think the msdn article needs some clarification.
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1843 Visits: 463
According to my books online

System and Sample Databases
mdf Mastlog.ldf model Model.mdf Modellog.ldf msdb Msdbdata.mdf Msdblog.ldf tempdb Tempdb.mdf Templog.ldf The system databases are master, model, msdb, and tempdb. Note: The default location of the database and log files is Program Files ...
Source: Installing SQL Server 2005


No mention of the resource database nor does it appear in my SQL Server Management Studio under System databases hence it is not a "real" system database.

What I did find about the resource "database"
>>
The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
<<

-- Mark D Powell --
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search