SQL2005: Reource database

SQL2005: Reource database

• Tmoeller

  Thanks Christian,

  I understand it does not contain User Data (I managed to read BOL too).  However it does contain (for example) a row in the equivalent of sysobjects for each user object.  This is what my query relates to.

  Cheers

  Rob

   

  
  

• armand

  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. The Resource database does not contain user data or user metadata. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions, upgrading involves dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is accomplished by copying the single Resource database file to the local server. This copy method also means that all that is required to roll back a service pack is an overwrite operation of the current version of the Resource database with the identified older version.

  
  
  

• videogamer2791

  Thanks Christian.

  I understand that all system views are actually bound to the Resource database that actually holds the system tables (which are hidden unless you query it via DAC).  As such, the Resource db MUST contain a catalog of user objects (although no user objects directly).

  So what happens if the Resource mdf is damaged   We can't recover as we cannot issue a backup against it.  So do we rebuild it   How does it populate itself with a catalog of user objects (especially since it's just "code")  

  Does anyone have any into on this

  Cheers

  Rob

  
  

• Twosheds

  A simple copy operation from the distribution disk.

  The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions, upgrading involves dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is accomplished by copying the single Resource database file to the local server. This copy method also means that all that is required to roll back a service pack is an overwrite operation of the current version of the Resource database with the identified older version.

  Also take a look at "Moving System Databases" http://msdn2.microsoft.com/en-us/library/ms345408.aspx

  
  
  

• Keith R. Smith

  From what I know, sysobjects is a view. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  Look at the definition of sys.objects for example:

  SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

   

  Result:

   

  CREATE VIEW sys.objects

  AS  

  SELECT name, object_id, principal_id, schema_id, parent_object_id,    type, type_desc, create_date, modify_date,    is_ms_shipped, is_published, is_schema_published   FROM sys.objects$ 

   

  How is sys.objects$ implemented I am not sure but I am sure that is bound to calling databases context.

  To test it use DAC and run

  USE MyDatabase

  GO

  SELECT name, object_id, principal_id, schema_id, parent_object_id,    type, type_desc, create_date, modify_date,    is_ms_shipped, is_published, is_schema_published   FROM sys.objects$ 

   

  It will show you the objects from MyDatabase.

  In theory in Resource database, you should not have user data. I hope that someone from MS will clarify this.

   

  
  
  
• SQL2005: Reource database