1

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.

  • You might be confusing the resource database with master? – Aaron Bertrand Sep 12 at 15:39
  • @AaronBertrand, I hope not. I'm was envisioning the sys schema. – J. Doe Sep 16 at 7:54
2

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.

  • 1
    Adding just a tiny bit: 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). – Tibor Karaszi Sep 13 at 6:52
  • @TiborKaraszi Thanks for mentioning that! I have added your note to my answer. – Solomon Rutzky Sep 13 at 7:20
  • Uhhh... isn't the sys schema actually the Resource Database? – J. Doe Sep 16 at 10:29
  • @J.Doe Can you please clarify what you believe is happening with the sys schema during a restore operation? At the moment I am guessing that you are referring to DB-specific meta-data such as what is found in sys.objects, sys.database_principals, etc. Am I correct? If so, then execute this: EXEC sp_helptext N'sys.tables';. You will see FROM sys.objects$ (where sys.objects also pulls from) as sys.tables is just a view. The definition might be in the resource DB, but the data for sys.objects$, sys.sysidxstats, etc (the actual internal system tables) is local. – Solomon Rutzky Sep 16 at 14:59
  • @SolomonRutzky, huh, OK. 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. And since I need some info from sys I wanted to know if it has data about objects in a restored database. – J. Doe Sep 16 at 15:17
7

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.

  • So after restoring a database from a backup, its objects will not exist in the sys schema? – J. Doe Sep 16 at 10:30
  • Also if it's read-only does that mean that any changes are never reflected in it? I read that if you were to upgrade your SQL server or something, copying the resource database and later pasting it on your new SQL server would effectively preserve/restore your objects and this was the Microsoft-advised method. So if it's read only how can that be possible? – J. Doe Sep 17 at 8:05

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.