disconnected import-export

Hi everybody,
I have the following problem: I have a very large data base used by our
clients for beta-testing. With prohibitions of just taking the data (using a
backup or so)
I can connect to this machine by accesing two terminal service - poor
network performance so on.
I have to take data out of this database - to have a proper "seed" for our
load test scenarios.
I tried bulk copy - well, I still have some problems with the inserts in our
database, but I am wondering wether is there a better way of automating the
task of exporting every table to a file using bcp - here comes the problem
wether I need a format file for every table)
Maybe I can achieve the same result in a more elegent way with DTS packages?
Is there a way to do this disconnected import export operation using dts and
some intermediery text/binary files that I can move between the machines?
Any help is welcomed,
Andreandrea,
Regarding moving data from one machine to another with bcp, you do not need
a format file for every table if: you:
1. Choose an overall format -c (character mode) or -n (native mode)
2. The table definitions between servers are identical.
Following is an example xfer.cmd file
-- BCPXFER.BAT
REM Parms %1=SourceServer %2=TargetServer %3=DatabaseName %4=TableName
%5=LogPath
bcp %3..%4 out \\Server\Xfer\%4.dat -c -S%1 -T -e%4-bcpout.log >> %5bcp.log
osql -S%1 -Q"truncate table %3..%4" -E >> %5bcp.log
bcp %3..%4 in \\Server\Xfer\ -c -S%2 -T -e%4-bcpout.log >> %5bcp.log
--
This makes some assumptions:
1. TRUNCATE TABLE will work on your target server, so there cannot be any
foreign key constraints for that to work. If there are FK constraints, you
will need to change to "delete table"
2. One trusted connection works on both servers. If this is not the case,
you will need to add more login information (including passwords) to the
parameter list.
3. Both servers can reach the same file share. If this is not true, then
you will need to insert a copy file step between the bcp out and the bcp in.
Etc. etc.
This is not a full solution for you, since there are many unknowns, but if
this works you then create a higher level BAT file in the form:
-- MyXfer.BAT
call bcpxfer.bat Server1 Server2 MyDatabase MyTable1 MyLogPath
call bcpxfer.bat Server1 Server2 MyDatabase MyTable2 MyLogPath
call bcpxfer.bat Server1 Server2 MyDatabase MyTable3 MyLogPath
RLF
PS - Are you the same person as Andrea Anastasescu?
"andrea" <a@.a.a> wrote in message
news:ex%23SKyB5HHA.464@.TK2MSFTNGP02.phx.gbl...
> Hi everybody,
> I have the following problem: I have a very large data base used by our
> clients for beta-testing. With prohibitions of just taking the data (using
> a backup or so)
> I can connect to this machine by accesing two terminal service - poor
> network performance so on.
> I have to take data out of this database - to have a proper "seed" for our
> load test scenarios.
> I tried bulk copy - well, I still have some problems with the inserts in
> our database, but I am wondering wether is there a better way of
> automating the task of exporting every table to a file using bcp - here
> comes the problem wether I need a format file for every table)
> Maybe I can achieve the same result in a more elegent way with DTS
> packages? Is there a way to do this disconnected import export operation
> using dts and some intermediery text/binary files that I can move between
> the machines?
> Any help is welcomed,
> Andre
>