Long shrouded in mystery, this nebulous hidden database has long been a source of burning curiosity to SQL Server administrators and developers alike. Like a mirage in the deep desert, as soon as you get close, it is no longer there. Like a shimmer seen from the corner of your eye, as soon as you look at it straight on, it disappears.
When the very early betas of SQL Server 2005 came out, and we were told that there was a new invisible system database, true SQL Server geeks were not content to just accept that at face value. We had to find a way to see what Microsoft did not want us to see. And a way was found...
Although in most situations, SQL Server does not show you this database, it does leave evidence that even a mediocre tracker can find. If you look in your SQL Server's default data directory (by default, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) you'll see the files for this database, which are not actually named 'Resource', but rather something quite a bit longer. The files are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are quite necessary to your SQL Server's operations. If you try to move, drop or rename these files while your SQL Server is running, you will get a message that the files are in use. If you stop your SQL Server and move, drop or rename one of these files, your SQL Server will not start.
These files support a database called mssqlsystemresource, which, as alluded to in the title of this post, is referred to as the Resource database. This is the database where the definitions of all programmable system objects are stored, such as your system stored procedures, system functions and all the definitions of the new metadata views. This includes the compatibility views like sysdatabases and sysobjects, as well as the catalog view like sys.databases and sys.partitions. For all practical purposes, this database is 'read only'. During upgrades, a new Resource database will replace the old, with enhancements to existing object definitions along with new object definitions. And because you can see the definitions of any objects using the sp_helptext procedure or the object_definition function, there should be no need to actually access this database.
But because it's there, we must. In early betas, geeks of my acquaintance quickly discovered that you could stop your SQL Server and copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf files. Your SQL Server can then be restarted normally, and then you can attach the two copied files under a new database name that becomes as accessible as any other user database. Of course, any change to the objects in this cloned database would have absolutely no impact on the real object definitions stored in the real Resource database.
A second method of access was determined closer to the time of SQL Server 2005's release. If you start your SQL Server in single user mode, the Resource database is available. This doesn't mean changing a database property to 'single user', it means stopping your SQL Server, and restarting with the -m flag.
I mentioned this little tidbit to the students in the SQL Server internals class that I was teaching in Melbourne a few weeks ago, and one of the students sent me a followup email indicating that he tried to do this. He started his SQL Server with -m, but could not 'see' the hidden database. So I decided to do some further investigation.
It turns out that even in single user mode, you still cannot see this hidden database. If you select from sys.databases, you will not see the database. But you can still USE the database:
USE mssqlsystemresource
You can verify you are in the Resource database, by running the following:
SELECT db_name()
It turns out that the definition of the sys.databases view has a specific exclusion for the Resource database. It joins several real system tables together, and then includes this filter:
WHERE d.id < 0x7fff
The value 0x7fff is 32767 in decimal, which is the id of the Resource database. You can see this for yourself by looking at the real system table. Inspecting the definition of sys.databases further, you can see that the 'd' prefix corresponds to the table sys.sysdbreg. As a real system table, you can only see the data in this table when you have connected to SQL Server using the Dedicated Administrator Connection (DAC). Once you have done that, you can see the names and id's of all databases:
SELECT id, name FROM master.sys.sysdbreg
Here's a subset of the output I get when I run the above query:
1 master
2 tempdb
3 model
4 msdb
5 AdventureWorks
6 pubs
7 Northwind
8 credit
16 Performance
32767 mssqlsystemresource
So you can now poke around all you want, but you'll probably find it rather boring. There really is nothing in the Resource database that you can't see using documented interfaces, but some people won't believe it until they see for themselves.
Have fun!
~Kalen