How to create Linked Server in Sql Server?

Open the Management Studio and in the Server Objects > Linked Servers, right click select New Linked Server menu.

image

In the appearing screen enter the actual remoteserver alias name as “RS”

image

Enter Product Name=’’, Data source=[YourRemoteServerInstanceName], Catalog=[DbName]

and press Ok button.

In the security tab enter map the sa user like following screen.

Note: If you are using another user to connect to the current server, use that user name for mapping.

 

image

Press Ok to complete the form.

Testing the Server

You can type any query with the prefix REMOTESERVER to access the tables.

SELECT * FROM RS.Database.dbo.Table

If you can see the results,  Congratulations!  You have done with linked servers

In case of error try the following

1. Change the Security Property (previous screen) “Be made using the security context:”

2. Set the Remote login and password in the textboxes.

3. Retry the query

Creating backup of table in remote server to local

SELECT * INTO new_table_name FROM RS.Database.dbo.Table

Leave a Reply

Your email address will not be published. Required fields are marked *