Open the Management Studio and in the Server Objects > Linked Servers, right click select New Linked Server menu.
In the appearing screen enter the actual remoteserver alias name as “RS”
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.
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