Locating the Resource system database

Question

There is a database called the Resource database, which I know that is read only and contains information about system objects and does not save any user related data or metadata.

I am using SSMS to manage my database instance and I can only see four system databases - master, model, msdb, tempdb. I'd like to know where can I find the Resource database using SSMS? Or is the database intentionally invisible for users?

Answer 1

The mssqlsystemresource database is hidden intentionally. The mssqlsystemresource is largely an implementation detail that allows the SQL Server installation and upgrade process to deploy new system objects versions without recreating objects in the master database as was done in SQL Server 2000 and earlier.

The physical location the mssqlsystemresource database files is a well-known documented location. In SQL Server 2008 and later versions, this is the instance BINN folder (e.g. C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn) together with other SQL Server binaries. In SQL Server 2005, the mssqlsystemresource were located in the same data folder as the master database files.

For learning, you can copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf to a user data folder, change file permissions, and attach the database using a different database name. This should only be done on an isolated test instance and will allow you to easily view the system objects in the mssqlsystemresource database using SSMS.

CREATE DATABASE mssqlsystemresource_copy
    ON(NAME='data', FILENAME='D:\SqlDataFiles\mssqlsystemresource.mdf')
    LOG ON(NAME='log', FILENAME='D:\SqlDataFiles\mssqlsystemresource.ldf')
    FOR ATTACH;
GO

Answer 2

I'd like to know where can I find Resource database using SSMS? Or is Resource database intentionally invisible for users?

You cannot find it using SSMS. The physical file location of resource database for versions SQL Server 2008 and later is

These files are located in :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\ and should not be moved

In SQL Server 2005 users were allowed to copy the resource database off to another location when they moved master database files. If you read SQL Server 2005 Master Database content it says that

The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location.

Since it was documented so users started moving it and playing around with it by moving to the different location. This has repercussions though many users complained that after moving resource database when they tried applying a service pack or CU update it failed.

The failure caused in SQL Server 2005 service pack upgrade which was eventually caused by movement of resource database location led to change in resource database location and it was decided that resource database would reside in the BINN folder along with the rest of the binaries. It's specifically in BINN folder because it does not contains any USER data only information related to SQL Server. Also, users would not touch files present in BINN folder.

Answer 3

Just adding that this indeed functions for all intents-and-purposes the same way on SQL Server on Linux. The System Resource Database files are in a different location; on my RHEL Server box, they are:

/var/opt/mssql/data/mssqlsystemresource.mdf
/var/opt/mssql/data/mssqlsystemresource.ldf

On my machine, I have user databases stored in /data/mssql/data and logs stored in /data/mssql/logs, so I needed to execute the following set of commands before attaching the System Resource Database to the instance:

> su - root
password:  <xxx>
> cp /var/opt/mssql/data/mssqlsystemresource.ldf /data/mssql/logs/SystemResourceDB.mdf
> cp /var/opt/mssql/data/mssqlsystemresource.mdf /data/mssql/data/SystemResourceDB.ldf
> chown mssql:mssql /data/mssql/logs/SystemResourceDB.mdf
> chown mssql:mssql /data/mssql/data/SystemResourceDB.mdf

Then, in SSMS, I used the following to attach the System Resource Database files:

CREATE DATABASE [SystemResourceDB]
ON 
      (FILENAME = N'/data/mssql/data/SystemResourceDB.mdf')
    , (FILENAME = N'/data/mssql/logs/SystemResourceDB.ldf')
FOR ATTACH;