CODEWITHSUNDEEP
sql-serversql-server-2008ssms
Adam
Adam

Reputation: 2465

Transfering Data From Production Server To Development Server

I am want to make the following statement:

INSERT INTO [Server_1\Instance_1].[Database].[dse].Table1
SELECT * FROM [Server_2\Instance_2].[Database].[dse].[**Table1**]

The point is the tables are on the different servers. I tried with the statement above. However, when I am on server_1 and when I run the following statement in order to retrieve the data from server 2:

SELECT * FROM [Server_2\Instance_2].[Database].[dse].[**Table1**]

... I get zero rows. But when I run the statement above on the Server_2, I get a set of rows.

How can I transfer data from production to development server/environments? I am using MS Management Studio.

UPDATE: The error message I am getting when I run the select statement in order to retrieve the data from another (production) server:

The OLE DB provider "SQLNCLI10" for linked server "Prod_Server\Instance" does not contain the table ""Database"."dse"."BoxIteration"". The table either does not exist or the current user does not have permissions on that table.

Select statement is:

SELECT * FROM [Prod_Server\Instance].[Database].[dse].[BoxIteration]

Upvotes: 0

Views: 3329

Answers (2)

Habeeb
Habeeb

Reputation: 1040

If u have access(Having credentials) to both prod and dev Db servers you can use "Import and Export Data" 1.Go to start and open "Import and Export Data" enter image description here

  1. Wizard will open give source server name, credentials and Database(For your case Prod)
  2. Then give destination server name, credentials and Database(Dev)
  3. Select the table , if there any identity column -->"Edit Mapping" and Enable identity insert.
  4. Then give next--> it will start copying.

Upvotes: 1

JotaBe
JotaBe

Reputation: 39075

Two things to take into account:

  • add the source server as a linked server in the destination server
  • verify that the credentials specified while creating the linked server have access to the data in the source server

I have a localized version of SQL Server, so the transalation may not match what you will see in your screen: open Object Explorer window in SSMS, connect to the destination server, open the tree and look for Server Objects, Linked Servers. Right click on the source linked server, and see the properties. Open the Security pane, and see which credentials are used to connect to the linked server.

Once you do so, you have to check the permissions of that credentials on the source server to verify that it can acccess the table in question.

If you don't understand, or can't do some of the steps (for lack of permissions), get help form your DBA: he will understand and solve the problem at once.

Upvotes: 2

Related Questions