We help IT Professionals succeed at work.

LinkedServer Connection to Same Database Server

maike9 used Ask the Experts™
I have two databases that have been backed up from production.  The stored procedures in one of the databases have references to a linked server connection to the other database.  In production, these two database reside on different servers.  I have backed up both databases and restored both of them to my default instance of Sql Server on my development machine.  My question is, is it possible to create a Linked Server connection back to the same instance of Sql Server so that the stored procedures work without any modifications to the linked server references?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ValentinoVBI Consultant
Most Valuable Expert 2011

Sure, here's how you could create it (the linked server is called "test" here):

EXEC sp_addlinkedserver @server='test', @srvproduct='', @provider='SQLOLEDB', @datasrc='YourServer\YourInstance'

More info: http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx
IT Engineer
Distinguished Expert 2017
Yes it is. Just put the local instance name in remote server parameter.