Resource database in sql server

Resource database is read only database includes all Sql server system objects. Resource database deals with sql server system Meta data whereas master database contains user metadata. It is use for compatibility changes that why it is not accessible to user.

Resource database makes sql server version upgrade much faster where as in old version of sql server, you have to drop and recreate or recompile all system objects to new version. Also it has ability to rollback service packs in its own way.

While sql server installation resource database also get created as mssqlsystemresource.mdf with default location of data file i.e “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Mssqlsystemresource.mdf”

Avoid renaming and moving of resource database file if you do so Sql server might not work properly or stop working.

If Sql server is having multiple instances then each Instance in Sql server has its own resource database file.  For accessing resource database, you have to start sql sever in single user mode and by running USE mssqlsystemresource statement you can access table inside resource database that are restricted to internal working of sql server 2005, these table are called as System base table.

 

To find out last update on resource database, you can use below statement.

SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’);

To find out current version of resource database, you can use

SELECT SERVERPROPERTY(‘ResourceVersion’);

To find out definition of “sys.object”, you can use object_definition function as given SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects’))

 

Reference: sql server 2005 books online

Leave a Reply

  

  

  


2 + 1 =