Is there a way to conduct a "Live to Play" data copy, whereby the data in the Production server can be replicated in the Development or QA servers.

Part of our training program, we will need trainees to be using "Real" data when learning how to use the system.

The data migration will be executed on a daily basis, thus the Development or QA servers will replicate the Production server data at the end of the day.

Any thoughts would be appreciated.

Hi Robbie,

I may suggest for you to have an SQL Job that will run at the end of the day.

The SQL Job must be able to:
   1) remove foreign key constraints  (alter table QA_Dev.Table1 no check constraint all)
   2) truncate data in your QA/Dev Server 
   3) replicate your data on your Production Server   (insert into QA_Dev.Table1 select * from Prod.Table1)
   4) bring back foreign key constraints 

I hope this would be helpful.
Mark Chua

Actually, the correct way of doing this is using the Environment Migration technical note.

Most of the times if you try to do it ad-hoc, you're going to have a bad time.
Ricardo's right. We do an environment migration about once every 3 months. It takes 3 - 4 hours of time, due to the amount of data being moved, the need to verify things, etc. We have to be EXTREMELY cautious when doing it, if we doit late at night when it is scheduled to bill credit cards, and we accidentally allow the Scheduler Service to run before we've cleaned the database, we could accidentally double bill thousands of people.

An environment migration is not to be taken lightly, and I definitely would not try to automate it.

One of the big issues with just trying to copy the data, is that the primary key values for static entities is non-deterministic. There is no guarantee that any particular environment uses the same keys for each record, which messes up your data. Also, if you push back to your Dev or QA environments, the schemas are likely to be different. As a result, when we do our environment migration, we do it immediately after a deployment to Production.

We keep our entities not in the Main database. Each app has it's own database. We would just like to restore one of these databases from Prod to DEV. The Environment Migration technical note seems to be just for the whole server .
How would we just copy one database for one app from Prod to DEV or to UAT.
I noticed that the table names and even field names are different between Prod and DEV. So it seems i cannot just copy the database from Prod on top of the one in Dev.
Any tips would be greatly appreciated.