Locating the Resource system database

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?

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

StackExchange Database Administrators Q#213494, answer score: 9