Is it better to copy objects such as tables to a remote server or to us a
datapump and push the data to another remote server?

It depends on your goal.

The good thing about the "Copy Objects" way is that should the
definition of the tables have changed on the source then this will be
reflected at the destination.

The bad thing is that you will be destroying and recreating the tables
every time. This will cost in time and performance. You will also want
to maybe take over all dependent objects as well.

Another bad thing is that you will have to throw ALL the data over to
the destination as well. When you transfer just the data then you can
move only the changed data. This could seriously reduce time and
energy.


So it is only "better" based on your intention.


Allan



"Shawn Ferguson" <shawn (AT) jards (DOT) com> wrote

Thank you for the thorough explaination.

I like the idea of Copying Objects. For my goal, I think I'll use transfer
so I only move the changed data without the overhead for creation and errors
from the changes.

Space is another issue, I have about 250,000 records and I need to move them
to another source as a backup. How could you reduce space, what
optimization techniques could you use.

I thought of using XML to copy across platforms, because it takes about an
hour to move it from SQL .



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I cannot imagine the verbose medium of XML would outperform.

Are you copying the whole thing across? If yes then a less error prone
way of doing things IMHO is BACKUP/RESTSORE.

If not then you could write a routine that exported to text file,
zippped, FTPs, decompressed, imported.

Your mileage may vary.

Allan


"Shawn Ferguson" <shawn (AT) jards (DOT) com> wrote