transfer records between two server????

Question

Post reply

transfer records between two server????

Papillon

Hall of Fame

Points: 3303
More actions
October 11, 2006 at 1:52 am

#76597

Hello freinds

I am really in vague situation ..i want to transfer 138954128 records from one server to another server?????
we have sql server 2000 and ram on old production server is 8 GB and new server is 2GB????
how i do this task and also i should consider log file grow and temp file grow????

pls guide me

Regards,
Papillon

Viewing 7 posts - 1 through 6 (of 6 total)

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

Holger Steidel SSChasing Mays Points: 602 More actions · Answer 1

Hi,

I have recently transfered around 270 millions of rows from one Server to another using the following:

Linked the destination server to the source server and then transfered the data with a select into a new table on the destination server and then applied the indexes on it.

I've set the database to simple recovery mode during that operation and I could see only a little grow of the log file.

Hope that helps.

Best regards,

Holger

CDBA Hall of Fame Points: 3266 More actions · Answer 2

Yes!! I also copy huge tables from one server to another / access to sql server. I keep destination database in simple recovery mode so that log space does not grow so much.

Hope this will help you.

CDBA

Papillon Hall of Fame Points: 3303 More actions · Answer 3

Hi

How to appy indexes i didnt get ur point..pls clear me

Regards,

Papillon

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

You might consider using BCP to write all the data from the source server to a file, then use it again to load the file into the destination table. If the destination database is in Simple or Bulk-logged recovery mode, this will avoid logging all the inserts and will be much faster than any form of INSERT/SELECT, DTS, or SSIS.

For best performance, run the BCP OUT command on the source server and the BCP IN command on the destination server to minimize network traffic.

MD-372277 SSCommitted Points: 1930 More actions · Answer 5

Using BCP is a good option if you are transferring all columns in the destination table otherwise the import will probably fail, it is a failing of BCP program.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

If the source and destination tables have different structures, you can create a view on the source system that matches the destination table and export from the view using BCP, or export the table to a text file and use a format file on the import to map the columns. Either way, you avoid the log file growth you would get with a single huge INSERT/SELECT.

You can put an INSERT/SELECT in a loop to transfer 1000 rows at a time to limit the effect on the log file, but some form of bulk insert will still give better performance.