How to copy tables from one server to another server?

  • 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

  • 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?

  • 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 (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

  • 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

  • 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).

  • 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

  • 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!

  • 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

  • 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

  • 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)

  • 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)

  • 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

  • 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

  • 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

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

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