how to move data fast

[alex]

Hello everybody
We need to move table T1 from database A to T1 database B on same server

size of table T1 15 GB and 40000000 rows

database B just created and will act as warehouse

could it be done simply by
1.creating table T1 on db B and then
2.set db to simple recovery
3.
insert into B.dbo.T1
select * from A.dbo.T1
4. create all the indexes on table T1 in db B

free disk space is 35GB

Any idea how to optimze import
Thank you

[MAK]

if that is the only big table,

I would take backup of the database and restore it as a different database and delete the un-necessary database

If not

1. DTS

or

transactional/snapshot replication, which basically BCP out and BCP in into new database.

[alex]

thank you
database A ~46 GB
and has only 1 big table

1. it takes 6 - 7 hrs to restore from
backup

2.any way to estimate time for dts export/import ?

[MAK]

1 Schedule it at night to restore the backup.

2. hard to say how long it will take. It depends on many things like below

a. speed of the processor
b. raid level
c. available memory
d. processor availablity
e. How you create the DTS package with how many transformation and with how many threads.

[rmiao]

In my experience, dts is much slower than restore.

[sdives]

Restoring for 6-7 hours seems like a long time. Are you restoring from tape? Could you backup to disk (local not network) then restore from disk.

My experience is that the restore will run 1/2 to 1/3 of the time.

I've also used BCP in native mode with moderate success for large tables. It seems that after the table reaches 10gb it seems to be more difficult to work with.

Sidney Ives
Database Administrator
Sentara Healthcare