Question for everyone:

What steps could we take to optimize table copy from one database to another?

A few days ago, I was running a large table copy from one database to another. Something like this:

This is a huge table, it has close to 6 billion records and the table is close to 300 GB in size. So, I wanted to find a way to optimize such a huge data transfer.

Some of the things considered:

• Table hints / Query hints to take exclusive lock on the table and use parallelism, but it wasn’t any faster.
• Data Compression to reduce the I/O. It was a bit faster, but still took a long time.
• Multiple data files on separate drives. Made it faster, but not fast enough.
• Made sure the MDF/NDF / LDF files have enough space so there is no AUTO_GROWTH

This is my second question: When back up of entire database can complete it 60 minutes, why does just one table copy take more than that? This is a lab environment, so there is no external traffic.

Any thoughts?

Thanks,
_Sqltimes