Post reply

Bulk copy from one server into another server.

  • January 31, 2019 at 10:24 pm

    #373659

    I have table contains fifty lakhs records and one of its column contains PDF data in blob column .I want to move this table records from one server into another Server.

    I tried below methods.

    1 Import and Export wizard
    2 Linked server.

    But it is taking more time.So any other bulk copy method or commands to transfer this table records from one server to another server 
    quickly.

  • February 1, 2019 at 5:35 am

    #2020832

    Try using Bulk Copy Program (BCP)

  • February 2, 2019 at 3:42 am

    #2020924

    u have any sample program?

  • February 2, 2019 at 6:36 pm

    #2020937

    If it's a one-off you could take a backup from one server and restore it on the other server then copy the data with an INSERT INTO... SELECT * FROM command.

  • February 3, 2019 at 3:51 am

    #2020944

    Good Idea.U are telling backup and restore the data base from one server into target server and then execute the above command.i think it is easy and copy the records from one database into another database in same server(target server) compare to copy the data from one server into another server.

  • February 3, 2019 at 4:27 am

    #2020946

    jkramprakash - Sunday, February 3, 2019 3:51 AM

    Good Idea.U are telling backup and restore the data base from one server into target server and then execute the above command.i think it is easy and copy the records from one database into another database in same server(target server) compare to copy the data from one server into another server.

    You can copy the data from one server to another with an INSERT INTO... SELECT * FROM command. But you would need to set up a linked server on one of the machines and use 4-part naming to access the linked server (LinkName.DatabaseName.SchemaName.TableName) If the network between servers isn't good it would make the insert slow. So restoring a backup onto the this machine you want to copy to should be faster and more reliable, also with linked server you don't get good index usage.

  • February 3, 2019 at 4:40 am

    #2020948

    Thank you.I will suggest this idea to my management.

  • February 3, 2019 at 5:29 am

    #2020951

    stick with the SSIS wizard and avoid a linked server.
     the SELECT INTO FROM linkedServer... will be a massive logging operation, and you might run out of disk space due to the amount of data, and it might bog down speedwise as it keeps expanding the log file by whatever your setting is(10%? 1 meg? etc)
    linked servers are notoriously slow.
    the import export wizard with migrate the data,creating two connections , one to each server, and do the migration  in chunks , migrating some number of rows that fit in x amount of memory, over and over until it completes.

    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!

  • February 3, 2019 at 5:39 am

    #2020953

    Thank you for the valuable suggestion.

  • February 3, 2019 at 4:35 pm

    #2020983

    jkramprakash - Thursday, January 31, 2019 10:24 PM

    I have table contains fifty lakhs records and one of its column contains PDF data in blob column .I want to move this table records from one server into another Server.

    I tried below methods.

    1 Import and Export wizard
    2 Linked server.

    But it is taking more time.So any other bulk copy method or commands to transfer this table records from one server to another server 
    quickly.

    In our terms, that's 5 MILLION PDF documents that you're trying to copy.  How large is this table in GigaBytes, please?

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

Viewing 10 posts - 1 through 9 (of 9 total)

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