Localdb linked servers

Question

I'm trying to replicate a production environment locally and the production database uses a linked server. I've been able to create multiple instances of localdb; is it possible to create a linkedserver between localdb instances? If not, what other options do I have available (ideally without having to use a full sql instance).

@Matt Does that work with localdb specifically? I don't appear to be able to communicate between the instances I've created. — Dave, Nov 22 '16 at 16:39

score 6 · Accepted Answer · answered Nov 23 '16 at 10:27

Not sure if it's bad form to answer your own question but in case anyone else has the same issue in the future, it turns out it is possible and pretty straight forward. Once you've created your new instance of localdb, use this:

USE master

IF EXISTS(SELECT * from sys.servers WHERE name = N'{serverName}')
BEGIN
    DECLARE @serverId INT
    SELECT @serverId = server_id FROM sys.servers WHERE name = N'{serverName}'
    IF EXISTS(SELECT * FROM sys.linked_logins WHERE server_id = @serverId)
    BEGIN
        EXEC sp_droplinkedsrvlogin '{serverName}', null
    END

    EXEC sp_dropserver '{serverName}'
END

EXEC sp_addlinkedserver     
   @server=N'{serverName}',   
   @provider=N'SQLNCLI',   
   @srvproduct=N'',
   @datasrc=N'{dataSource}';  
EXEC sp_addlinkedsrvlogin
    @rmtsrvname=N'{serverName}',
    @useself='true'

To clarify the server name (`@datasrc` parameter) to use for your localdb instance: Use the display name you see in SQL Server Management Studio Object Explorer, or Azure Data Studio. Don't use the name returned from `SELECT @@SERVERNAME`. For example, if your localdb instance appears as `(localdb)\mssqllocaldb` in SSMS Object Explorer, `@@SERVERNAME` will return something like `{host machine name}\LOCALDB#6B537A3C`. In `sp_addlinkedserver` use `@datasrc=N'(localdb)\mssqllocaldb'` — Simon Elms, Feb 26 '23 at 22:54

score 6 · Answer 2 · edited Feb 08 '17 at 01:28

To expand on Dave's auto-answer a little...

If the remote database has not been attached to its instance yet, it will be necessary to include additional connection details in the Provider String argument of sp_addlinked server.

EXEC sp_addlinkedserver     
   @server=N'{serverName}',   
   @provider=N'SQLNCLI',   
   @srvproduct=N'',
   @datasrc=N'{dataSource}'
   @provstr=N'{providerString}';

In my case I used:

{dataSource} = (LocalDB)\MSSQLLocalDB

{providerString} = AttachDbFileName=C:\Temp\Test.mdf;Integrated Security=True

Localdb linked servers

2 Answers2