5

In my default install of SQL Server 2017 on Linux, under /var/opt/mssql/data I see the following databases,

  • master.mdf - master Database - Records all the system-level information for an instance of SQL Server.
  • model.mdf - model Database - Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
  • msdbdata.mdf - msdb Database - Is used by SQL Server Agent for scheduling alerts and jobs.
  • tempdb.mdf - tempdb Database - Is a workspace for holding temporary objects or intermediate result sets.

All of these databases are actually pretty well documented. However my install's omission of the Resource Database stands out. The description of the Resource Database seems to indicate that it is its own database,

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. 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.

Even more confusing, for SQL Server 2012 the docs say, (not 2017 which is what I'm running)

Physical Properties of Resource

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

I don't see a mssqlsystemresource.mdf anywhere though. Where is this database stored? Is this compiled into the server itself?

Checking it out, I started up database in single-user mode and connected to mssqlsystemresource and started digging,

1> USE mssqlsystemresource
2> GO
Changed database context to 'mssqlsystemresource'.
1> SELECT type, type_desc, physical_name FROM sys.database_files;
2> GO
type type_desc                                                    physical_name                                                                                                                                                                                                                                                       
---- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   0 ROWS                                                         d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf                                                                                                                                                        
   1 LOG                                                          d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.ldf                                                                                                                                                        

(2 rows affected)

I don't see these .ldf or .mdf anywhere though. And, being a Linux install I'm guessing d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\ is a virtualized internal directory.

Further sys.master_files lacks the db too

1> SELECT LEFT(name,20), LEFT(physical_name,35) FROM sys.master_files WHERE name LIKE 'ms%';
2> GO

-------------------- -----------------------------------
MSDBData             /var/opt/mssql/data/MSDBData.mdf   
MSDBLog              /var/opt/mssql/data/MSDBLog.ldf    
(2 rows affected)

When I start up the database in single user mode sudo -u mssql /opt/mssql/bin/sqlservr -m I see this,

spid9s   Starting up database 'mssqlsystemresource'.
spid9s   The resource database build version is 14.00.3008. This is an informational message only. No user action is required.
5

I've confirmed that this Resource Database is packed in the archive, /opt/mssql/lib/sqlservr.sfp along with the rest of the files in Binn. So the answer to the question is that it resides in the sfp archive. More to the answer, you can extract that sfp with the method here creating a new file with the contents,

/opt/mssql/lib/sqlservr/Content/binn/mssqlsystemresource.mdf

So the contents there are likely virtualized in the In the Drawbridge layer as

d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.