T-SQL query to find "mssqlsystemresource" database ID and database files location | SansSQL

Tuesday, May 28, 2013

T-SQL query to find "mssqlsystemresource" database ID and database files location

We all know that mssqlsystemresource is a system database that is introduced from SQL Server 2005 onwards.
Here are few links which I posted earlier which gives more information about mssqlsystemresource database.

SQL Server 2005's 5th system database - MSSQLSystemResource (The Invisible Database): http://www.sanssql.com/2008/09/sql-server-2005s-5th-system-database.html

Resource Database in SQL Server 2008:
http://www.sanssql.com/2010/07/resource-database-in-sql-server-2008.html

So this is also a database which is part of the database engine and plays vital role within the system.
As a general rule, every database should have an ID and now the question is what is the ID of mssqlsystemresource database and how to find it?
A simple select statement on sys.databases will not give the details about mssqlsystemresource database.
So to find the Database ID of mssqlsystemresource
Login to the database instance using Dedicated Administrator Connection (DAC). The below link explains how to use DAC.
http://www.sanssql.com/2010/07/dedicated-administrator-connection-dac.html
Once you login using DAC, use the below query to find the database ID of mssqlsystemresource database.
SELECT * FROM sys.sysdbreg WHERE name='mssqlsystemresource'
From this query we now know that the database ID of mssqlsystemresource database is 32767.

What next?

Now open a "New Query" (this can be normal connection) and execute the below query to find where the database files of mssqlsystemresource database are located. 
SELECT dbid,name,filename FROM sys.sysaltfiles WHERE dbid = 32767

No comments:

Post a Comment