Is the master db aware of user dbs?

Question

Where does SQL Server record the presence/paths of user dbs?

I tried following approaches:

Approach 1. I restored a master db on svr2 from a backup from svr1

Apprpach 2. I replaced the master db data/log files of svr2 with with copy from svr1

In both approaches, the GUI creates the user dbs on svr2 in recovery pending mode. Hence I want to ask where exactly does sql server store the information about whether user dbs are attached to it and their paths.

Answer 1

If you consider the boot process, then all will probably be understandable:

1. SQL Server finds the path to the two database files for master from the registry.
2. SQL Server recovers master.
3. SQL Server has a system table (remember that these are hidden from us) with one row per database. We can deduce from the execution plan when you select from sysdatabases that the name of this hidden table is sys.sysdbreg. We can even SELECT from it if we connect through the DAC. This doesn't have the path for each database's primary file, so some internal method hidden from us is used to get that.
4. SQL Server recovers model.
5. SQL Server creates tempdb based on model and the "file template" for tempdb (as seen for us through sys.master_files).
6. SQL server recovers the each other database.

I.e., SQL Server has the path to the MDF file for each database in master. The MDF file, in turn has the path to each other file for that database.

Steps (4,5) and (6) can possibly be done in parallel and not neccesariuly in that sequence. If any of steps (1-2), 4 or 5 fails then SQL Server will shutdown ("fail to start").

The reason for sys.master_files to exist is in case you lose the mdf file for some database, you want to be able to do a tail log backup of that database. That wasn't possible in 7.0 when this redundant information for each database's non-mdf didn't exist in master. (How can MSSQL do a tail log backup when it doesn't know where the ldf file is?) I.e., in 2000, MS added a system table which back then was called sysaltfiles, for this very purpose. The info from sysaltfiles transformed into what we see as the view sys.master_files nowadays.

The key internal table underlying sys.master_files is sys.sysbrickfiles. You can read from that system table using the DAC. It includes the logical (lname) and physical (pname) names of every file in every database.

Answer 2

The master database only has meta-data about the user databases. Meta-data is just data describing the user databases (such as a string of their file paths), it doesn't store the actual files of the user databases themselves (which have the user data).

Therefore restoring just the master database wouldn't automatically create those files for you. You need to also restore the user databases to that SQL Server instance on svr2.