How to copy tables from one server to another server?

Question

How to copy tables from one server to another server?

Charmer

SSChampion

Points: 14887
More actions
June 5, 2012 at 11:15 am

#262450

Hi Friends,
How to copy tables from one server to another server?
i tried ssis and export/import through ssms....but it says server instance is timed out to get the connection....
since the table is too much in size...i am not able to generate scripts...
is there any option i can try?
Help me, friends....
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

Charmer (6/5/2012)
Hi Friends,
i tried ssis and export/import through ssms....but it says server instance is timed out to get the connection....

Have you established that your two servers can talk to each other? Can you telnet or ping one server from another?

Geoff A SSChampion Points: 11511 More actions · Answer 2

it depends on what the table contains. (filestream data, identity column, timestamps, etc...)

but i like to do the following (your mileage will vary);

1. create the database where the data will go.

2. script out the table

3. run the create table script on new database.

4. create a linked server to instance where data is.

5. use 4 part naming to insert into new table.

the thing to be aware of when doing this is your log drive. It has to have ALOT of free space to do this.

but SSMS should do this with the import / export utility. if it is timing out, you might have bigger issues that need resolved first.

Charmer SSChampion Points: 14887 More actions · Answer 3

Charmer (6/5/2012)
Hi Friends,
How to copy tables from one server to another server?
i tried ssis and export/import through ssms....but it says server instance is timed out to get the connection....
since the table is too much in size...i am not able to generate scripts...
is there any option i can try?
Help me, friends....

i don't know how to do that?

Could you explain me please?

Thanks,
Charmer

Charmer SSChampion Points: 14887 More actions · Answer 4

Geoff A (6/5/2012)
it depends on what the table contains. (filestream data, identity column, timestamps, etc...)
but i like to do the following (your mileage will vary);
1. create the database where the data will go.
2. script out the table
3. run the create table script on new database.
4. create a linked server to instance where data is.
5. use 4 part naming to insert into new table.
the thing to be aware of when doing this is your log drive. It has to have ALOT of free space to do this.
but SSMS should do this with the import / export utility. if it is timing out, you might have bigger issues that need resolved first.

i tried Linked server but it is also timing out...

i don't know how to resolve this...:ermm:

Thanks,
Charmer

Geoff A SSChampion Points: 11511 More actions · Answer 5

change the Remote Query timeouts.

Right click on instance name in SSMS, choose Properties, click on Connections.....

it should be set to 600 seconds (default).

Charmer SSChampion Points: 14887 More actions · Answer 6

Geoff A (6/5/2012)
change the Remote Query timeouts.
Right click on instance name in SSMS, choose Properties, click on Connections.....
it should be set to 600 seconds (default).

i already tried by changing it to 60000...but it ain't worked...

Thanks,
Charmer

Lowell SSC Guru Points: 323482 More actions · Answer 7

don't try to move the entire table in one pass;

change it to do a few rows at a time instead, and confirm it's working that way.

tweak the number of rows to something that is more bite sized and acceptable performance wise.

SET ROWCOUNT 100

WHILE 1=1

BEGIN

DECLARE @NewID int

SELECT @NewID = max(PKID) From LinkedServer.Archived.dbo.MyTable

IF @NewID IS NULL SET @NewID = 0

INSERT INTO LinkedServer.Archived.dbo.MyTable (PKID ,ColumnList)

SELECT ID,ColumnList FROM Production.dbo.MyTable WHERE PKID > @NewID ORDER BY PKID

IF @@ROWCOUNT = 0

BREAK

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 8

What query is timing out? For example, are you trying to do this all at once or one table at a time? If it is only 1 table, how may rows are in it and what is the DDL of the table? I would like to determine if it is a data issue or if it is something else...

Jared
CE - Microsoft

spaghettidba SSC Guru Points: 105712 More actions · Answer 9

Charmer (6/5/2012)
Hi Friends,
How to copy tables from one server to another server?
i tried ssis and export/import through ssms....but it says server instance is timed out to get the connection....
since the table is too much in size...i am not able to generate scripts...
is there any option i can try?
Help me, friends....

Connection timing out means there's something wrong here.

First thing to check is connectivity:

Can you connect to the target server from your workstation?

The import / export wizard runs from your machine, not from one of the servers, unless you schedule the package execution with SQLAgent.

If you can't copy the table with SSIS (Import / Export wizard generates a SSIS package), you could use BCP to dump the table to a text file, copy to the target machine and bulk load it.

Another option is the copy database wizard, but again it generates a SSIS package.

A linked server, given that the source instance can connect to the target instance (or the other way round) would certainly work for a small amount of data, but it's a very poor choice when the data volumes are huge.

Hope this helps

Gianluca

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

IIRC, Replication actually uses BCP in the "native" mode to do the first synchonization. It's very fast, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Charmer (6/5/2012)
Geoff A (6/5/2012)
it depends on what the table contains. (filestream data, identity column, timestamps, etc...)
but i like to do the following (your mileage will vary);
1. create the database where the data will go.
2. script out the table
3. run the create table script on new database.
4. create a linked server to instance where data is.
5. use 4 part naming to insert into new table.
the thing to be aware of when doing this is your log drive. It has to have ALOT of free space to do this.
but SSMS should do this with the import / export utility. if it is timing out, you might have bigger issues that need resolved first.
i tried Linked server but it is also timing out...
i don't know how to resolve this...:ermm:

Do you have any indexes on the target table that have what some call "low cardinality"? In otherwords, an index on a column with just a few unique values?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Charmer SSChampion Points: 14887 More actions · Answer 12

Jeff Moden (6/5/2012)
Charmer (6/5/2012)
Geoff A (6/5/2012)
it depends on what the table contains. (filestream data, identity column, timestamps, etc...)
but i like to do the following (your mileage will vary);
1. create the database where the data will go.
2. script out the table
3. run the create table script on new database.
4. create a linked server to instance where data is.
5. use 4 part naming to insert into new table.
the thing to be aware of when doing this is your log drive. It has to have ALOT of free space to do this.
but SSMS should do this with the import / export utility. if it is timing out, you might have bigger issues that need resolved first.
i tried Linked server but it is also timing out...
i don't know how to resolve this...:ermm:
Do you have any indexes on the target table that have what some call "low cardinality"? In otherwords, an index on a column with just a few unique values?

Nope.....Jeff, i don't have any index on source column...and i am trying to copy the tables to the destination where there is no such tables are already existed...

Thanks,
Charmer

Charmer SSChampion Points: 14887 More actions · Answer 13

SQLKnowItAll (6/5/2012)
What query is timing out? For example, are you trying to do this all at once or one table at a time? If it is only 1 table, how may rows are in it and what is the DDL of the table? I would like to determine if it is a data issue or if it is something else...

i just want to copy 10 tables from the source database where each tables consists of 70,000 records averagely...

Thanks,
Charmer

Charmer SSChampion Points: 14887 More actions · Answer 14

Gianluca Sartori (6/5/2012)
Charmer (6/5/2012)
Hi Friends,
How to copy tables from one server to another server?
i tried ssis and export/import through ssms....but it says server instance is timed out to get the connection....
since the table is too much in size...i am not able to generate scripts...
is there any option i can try?
Help me, friends....
Connection timing out means there's something wrong here.
First thing to check is connectivity:
Can you connect to the target server from your workstation?
The import / export wizard runs from your machine, not from one of the servers, unless you schedule the package execution with SQLAgent.
If you can't copy the table with SSIS (Import / Export wizard generates a SSIS package), you could use BCP to dump the table to a text file, copy to the target machine and bulk load it.
Another option is the copy database wizard, but again it generates a SSIS package.
A linked server, given that the source instance can connect to the target instance (or the other way round) would certainly work for a small amount of data, but it's a very poor choice when the data volumes are huge.
Hope this helps
Gianluca

Sartori, actually i used to connect the work station through logmein and then Remote desktop from there... Here i tried to connect my target server which i could not...

Thanks,
Charmer