Hi,
I am asked to move all data from one database in Production to Development
everynight.
When I try to use the Import/Export Wizard, it fails because when the data
in Development Server is droped, it will violate the Foreign Key Constraint
because it drops One side of the table first without dropping many side.

What I can think of to achieve that is to script out all the contraints
first and after the DTS migration, add all the contraint scripts back in.
Or
I create a package and look at the ERD to delete the Many side data in
tables first then delete the One side tables and when I do the data
transformation, I will insert the One side first, then Many side.

Which way is better or any better soluction?

Thanks

Ed

If you are really moving ALL the database then a far simpler and tighter
solution is BACKUP and RESTORE

Allan

"Ed" <Ed (AT) discussions (DOT) microsoft.com> wrote

Alan,
Thanks for your suggestion. How am I able to schedule a job that will
move the backup file from Prod to Dev and restore it from Dev?

Thanks again.

Ed

"Allan Mitchell" wrote:

This is what I do

Create a BACKUP job on Prod.
Have it backup to a location on the Network or even the Dev server

This is where we can branch into two

Method 1

1. On the dev server have a table that takes the location of the BACKUP
2. On there is a trigger which does your RESTORE (ON INSERT)

Method 2

Have the production job kick off a RESTORE job on the Dev server .


Allan




"Ed" <Ed (AT) discussions (DOT) microsoft.com> wrote