Posted by Plamen Ratchev on 01/30/07 05:06

Since you rule out the traditional methods for BACKUP/RESTORE, then maybe
you can take a look at linked servers (that is if you have direct
connectivity between the two servers, which I assume you do because you
mention replication). You can set up a remote linked server and then
directly query and transfer the tables that you need. It can be easily
automated via a stored procedure that can be scheduled to run as a job.
Simple enough. Not the fastest approach but seems you are already looking at
generating INSERT statements to dump the data out...

Another alternative is to use a DTS package to transfer the tables. It can
be also fully automated, but assumes as above that you have connectivity
between the servers.

If you do not have direct connectivity then you can use a similar approach
to dumping with INSERT statements, but via DTS. In essence in the source
server you can create a DTS task to dump the table data to a text file, then
on the destination server import the data to a table using the reverse
process. The same scenario is doable using the BCP utility.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]