Find old linked servers on a restored copy of master/msdb db

Question

We moved our dbs to a new server and the drives on the old server are still available, but the SQL service is offline and cannot be started.

I want to find the linked servers that were registered on the old server. I tried restoring the master db under a different name on another SQL server. That's all fine, but when I tried to run

select * from newmaster.sys.servers

it actually looks in the actual master db of that server instead of what I specified?

How do I view the list of linked servers in this case?

Answer 1

The system catalogs (SYS objects) are stored in the resource DB. The resource DB is usually located at: <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, i.e. the same location of the master DB. Yes, this is like the fifth system database.

Check this link on: Moving the master and Resource Databases to move your master and resource DB.

Answer 2

Using sp_helptext lookup the definition of sys.servers. You'll see that is a view that selects from a system catalog table. Then from a DAC connection, select directly from the same system catalog table in [newmaster].

Answer 3

Try the following:

select * from sysservers

If that does not work (version dependent) I suggest you fire up a new instance of SQL, then restore your old master database there.

If you cannot do that, you can comb through the source code for the stored procedures and views, looking for references to linked servers.

Answer 4

The answer by Remus was spot on. But since I needed to dig a bit more, here's more details. This is tested on SQL Server 2017, but the principle should be the same.

Check the following view definitions. I was mostly looking for user mappings, but if you need more info about servers, you find those tables from the first view.

sp_helptext 'sys.servers'
sp_helptext 'sys.linked_logins'
sp_helptext 'sys.server_principals'

You find that tables you need are sys.sysxsrvs, sys.syslnklgns and sys.sysxlgns.

Restore the master database from old server to different name.

Connect to server using DAC connection.

Now read the data from those tables. From here you find servers, the linked login definitions and login ids used in those definitions.

USE oldserver_master
GO
SELECT * FROM sys.sysxsrvs
SELECT * FROM sys.syslnklgns
SELECT * FROM sys.sysxlgns