Does restoring a database in Microsoft SQL Server add objects to Resource Database?

Question

As far as I know the resource database contains information on all objects in its server instance, so I would think it's only natural that when you restore a database from a backup file, part of the process would be to register its objects in the server's resource database but I can't find any concrete information on the subject and I'm unable to test it locally.

Answer 1

In addition to what Scott said about the Resource DB — [mssqlsystemresource] — that it's read-only (which is entirely correct; it's SQL Server application data), it needs to be stated that no database "contains information on all objects in its server instance". That concept does not exist in SQL Server: not for [mssqlsystemresource], nor [master], nor any other database. In fact, no database actually contains information on objects in other databases. Sure, you can reference objects in another database, but there is no knowledge within the calling database that such objects even exist (well, not until execution time).

The closest you can get of knowledge of other DBs is:

1. a subset of database meta-data, which is exposed as most, but not all, of the fields in the sys.databases system catalog view. Some of those fields (e.g. collation_name, target_recovery_time_in_seconds, delayed_durability, delayed_durability_desc, etc) are not known if the database is either closed or offline.

2. and, as Tibor Karaszi noted in a comment on this answer:

   The closest we come to "having stuff from other databases centralized" was introduced in SQL Server 2000. In 7.0, you couldn't do a log backup for a broken database, since SQL server didn't know where the ldf file is. So in 2000, MS "mirrored" the stuff in each databases' sysfiles into master..sysaltfiles. And this is, of course, what we today see as sys.master_files (and it is in the master db, physically).

So, even if the Resource DB were not read-only, you would still not be able to find any information on the restore process registering the restored DB's objects into any other database (because that's not actually part of the process).

Also, the "Resource" DB can be found at the following path:

C:\Program Files\Microsoft SQL Server\MSSQL{2-digit_version_number}.{instance_name}\MSSQL\Binn\mssqlsystemresource.[m|l]df

As you can see, it's in the \Binn directory / folder as it is purely read-only application meta-data. It does not get backed-up or restored (well, not unless you back up the \Binn folder).

From "Resource database is a read-only system database which is hidden from users. System objects such as sys.objects are physically stored in Resource Database which logically appears in the sys schema of each database." I figured that sys schema = the resource database and when I do a select * from sys.tables I'm actually selecting from the resource database.

Please keep in mind that sys.objects is merely a view (a system catalog view). If you execute EXEC sp_helptext N'sys.objects'; you will see that it selects from sys.objects$. What that quote means is that the meta-data for system objects exists in the Resource DB. This would be the definitions of stored procedures, functions, DMFs, DMVs, views, the Microsoft.SqlServer.Types assembly (which contains the code for the HierarchyID, Geometry, and Geography datatypes), and the initial schema (as in table structure, not a "Schema") for internal system tables such as sys.objects$, sys.syssingleobjrefs, sys.syspalnames, and so on. Executing select * from sys.tables pulls DB-specific data from the DB itself. The only info stored in the Resource DB that some system catalog views pull from is static lookup data — from sys.syspalnames — which is the values for the *_desc columns (e.g. type_desc) and probably other similar types of columns.

Answer 2

Well, reading the documentation for Resource Database (highlighting mine)

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.

Given the above and the fact that it is READ-ONLY, I don't see how restoring a database updates the resource database.