SQL Server Loopback Linked Server

Occasionally I run across companies that make use of linked servers. This can be a nasty little detail in a development environment to fully test database code. The solution is to create a loopback linked server so that code referencing a linked server will run as if it were running in the distributed production environment.

  1. Get a backup of the database on the other end of the linked server call.
  2. Restore the backup on your local SQL Server instance
  3. Run the code below to create the loopback linked server

EXEC sp_addlinkedserver @server = N’name_for_linked_server’,

@srvproduct = N’ ‘,

@provider = N’SQLNCLI’,

@datasrc = N’name_of_my_sqlserver_instance’,

@catalog = N’name_of_database’

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s