Board index » delphi » Copying Huge Data one DB to another (MS SQL Server)

Copying Huge Data one DB to another (MS SQL Server)


2005-06-24 10:37:59 PM
delphi194
Hi,
I hat to copy data from one table to another database table that runs on the
same machine.
1.Database A contains table A.
2.Table A has 2 million rows.
3.There are image and text fields.
4.Total table size approximately 55GB.
5.I created a database B in the same server and Table B in DB B.
6.I had to copy all table to Table B.
7.Table A runs on IDE disk which is size 150GB.
8.Table B runs on SCSI RAID 5 which is size 75GB.
what is the fastest way to copt table data?
My Test Results;
1.I copy with primary index range, (10.000 at a time) . I will take 10 days.
2.I copy the data with insert into, log file exceed the size limit (75GB
total)
3.I copy the data one by one. Up to one level, its fast. in about 100.000
record, memory sucs. SQL server uses 1.8 GB memory. Than slow downs.
The server has 2 CPUs and 2GB memory.
Any idea?
 
 

Re:Copying Huge Data one DB to another (MS SQL Server)

How about backup db A, than restore it as db B and drop there all
unnecessary tables. Also DTS could be an option here.
BTW do you really need to copy such a huge amount of data?
 

Re:Copying Huge Data one DB to another (MS SQL Server)

"Gürcan YÜCEL" <XXXX@XXXXX.COM>writes:
Quote
The server has 2 CPUs and 2GB memory.
Any idea?
Try dropping indexes and then putting them back on when your copy is
complete!
Paul...
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0=6;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
 

Re:Copying Huge Data one DB to another (MS SQL Server)

Quote
>The server has 2 CPUs and 2GB memory.
>Any idea?
bcp and BULK INSERT...
hth,
brian
--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com
 

Re:Copying Huge Data one DB to another (MS SQL Server)

Table B structure different from Table A. This is an upgrade process from
one software to another.
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Quote
How about backup db A, than restore it as db B and drop there all
unnecessary tables. Also DTS could be an option here.
BTW do you really need to copy such a huge amount of data?


 

Re:Copying Huge Data one DB to another (MS SQL Server)

Then DTS is the way to go.
"Gürcan YÜCEL" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅ?
Quote
Table B structure different from Table A. This is an upgrade process from
one software to another.

"Vitali Kalinin" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>How about backup db A, than restore it as db B and drop there all
>unnecessary tables. Also DTS could be an option here.
>BTW do you really need to copy such a huge amount of data?
>
>


 

Re:Copying Huge Data one DB to another (MS SQL Server)

On Fri, 24 Jun 2005 17:09:59 +0100, Gürcan YÜCEL <XXXX@XXXXX.COM>
writes:
Quote
Table B structure different from Table A. This is an upgrade process from
one software to another.
DTS. And if this is a one-off, then speed isn't really important.
--
jc