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
[quoted text, click to view] "Ed" <Ed@discussions.microsoft.com> wrote in message
news:Ed@discussions.microsoft.com:
> 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:
>
> > If you are really moving ALL the database then a far simpler and tighter
> > solution is BACKUP and RESTORE
> >
> > Allan
> >
> > "Ed" <Ed@discussions.microsoft.com> wrote in message
> > news:Ed@discussions.microsoft.com:
> >
> > > 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
> >
> >
