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.
- Get a backup of the database on the other end of the linked server call.
- Restore the backup on your local SQL Server instance
- 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’