The fastest way I perform a reliable refresh on SQL is to BCP Extract all the tables, truncate the destination tables and then reimport them.
Depending on the size of your F0911 depends on how long the process takes. Here are some clues :
First of all, the extract :
[ QUOTE ]
s:
mkdir s:\backups\database\dba\PROD_DATA
Cd s:\backups\database\dba\PROD_DATA
start /B bcp JDE_PRODUCTION.PRODDTA.F0000194 out PRODDTA_F0000194 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
start /B bcp JDE_PRODUCTION.PRODDTA.F00022 out PRODDTA_F00022 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
start /B bcp JDE_PRODUCTION.PRODDTA.F00023 out PRODDTA_F00023 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
.
.
start /B bcp JDE_PRODUCTION.PRODDTA.FD3N915 out PRODDTA_FD3N915 /b 100000 /n /t /Ssvowsql /UPRODDTA /P{proddta password}
[/ QUOTE ]
Note that all the extracts run in parallel. With 3,000 tables in oneworld - you'll see literally 3000 BCP processes start up - there doesn't seem to be any limitation on extracting out the data. The majority of the BCP processes will be extracting out empty or very small tables - and will start and finish in a few seconds. The final few will be the F0911 and the really big tables in your system. Depending on the speed of your system, a full 150Gb production database takes around 35 minutes to extract to disk (ie, the F0911 takes about that long to complete). I run this every night as part of my backup - since it enables me to individually restore tables upon request. I also keep a few "archive" (zip) versions of this directory around for a few days, just in case. It means I usually don't have to go back to tape if I have to restore something !
Next, truncate your database :
[ QUOTE ]
if exists (select * from dbo.sysobjects where id = object_id(N'[TESTDTA].[F0000194]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTDTA].[F0000194]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[TESTCTL].[F0002]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTCTL].[F0002]
GO
.
.
.
if exists (select * from dbo.sysobjects where id = object_id(N'[TESTDTA].[FD3N915]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
truncate table [TESTDTA].[FD3N915]
GO
[/ QUOTE ]
Obviously, this script is created simply enough from inside SQL Enterprise Manager.
Lastly, the restore from BCP file :
[ QUOTE ]
Cd PROD_DATA
start /B bcp JDE_DEVELOPMENT.TESTDTA.F0911 in PRODDTA_F0911 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f0911.txt" -b10000 -m50 -h "TABLOCK"
start /B bcp JDE_DEVELOPMENT.TESTDTA.F42199 in PRODDTA_F42199 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f42199.txt" -b10000 -m50 -h "TABLOCK"
bcp JDE_DEVELOPMENT.TESTDTA.F0015Z1 in PRODDTA_F0015Z1 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f0015z1.txt" -b10000 -m50 -h "TABLOCK"
start /B bcp JDE_DEVELOPMENT.TESTDTA.F001651 in PRODDTA_F001651 /n /t /Ssvowsql /UTESTDTA /P{testdta password} /o "s:\output_f001651.txt" -b10000 -m50 -h "TABLOCK"
.
.
.
[/ QUOTE ]
Note that here, the F0911, F42199 and F0015Z1 will start in parallel - then the script will pause until the F0015Z1 finishes before continuing on starting F001651 in parallel. I do this to "stutter" the process somewhat - running 3000 imports in parallel causes the system to actually cause issues - ending up with some tables not actually restoring properly (a microsoft resource limitation somewhere along the line). In effect, I place my restores in order based on largest through to smallet, and delete the "start /b" wherever I want to create a "pause".
I also run the tablelock hint - so make sure that nobody is on your system in DEV when you restore, otherwise they'll get into a deadlock. It can take a few hours to perform the full restore - ie thats how long the F0911 takes to restore because of all the indexes.
Lastly, there IS indeed a way to get the JDE Table Copy function to run JUST as fast as a SQL refresh - its all about running the process in parallel. I have another customer that uses JDE to perform their refresh and the 9 Table Conversion UBE's take abut 4 hours to completely refresh their data. That way, I can even schedule it if I wish (!)
Hope that helps - email/phone if you have any questions.
By the way - I expect to be flamed over using BCP, compared to DTS - but if you want to use DTS to do this - knock yourselves out (I just like using scripts and automating most of it. I hate to babysit a GUI process !)