Is the master db aware of user dbs?
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.
If you consider the boot process, then all will probably be understandable:
- SQL Server finds the path to the two database files for master from the registry.
- SQL Server recovers master.
- 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.
- SQL Server recovers model.
- SQL Server creates tempdb based on model and the "file template" for tempdb (as seen for us through sys.master_files).
- 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.