2

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?

3

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.

| improve this answer | |
1

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.

| improve this answer | |
1

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].

| improve this answer | |
  • I was not able to look up the definition of sys.servers, but was able to look up sysservers which is a view that uses sys.servers. I connected using DAC and did a select on sys.servers, but the results were the same as what's stated in the question :(... – deutschZuid Mar 12 '12 at 20:34
1

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
| improve this answer | |

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.