I have 2 database tables.
1) From Production
2) From Dev.
The task is to move the data refresh from Production to Dev Server. But the table schema itself is changed is getting changed and also Primary key & unique key was not implemented properly.
Can anyone help, what will be best process to the data fresh ?
I had tried to copy the table from Prod to dev and check the count of the tables everything looks goods but the table schema , primary key constraint and related key definition are getting changed
this is where change management for DDL is absolutely mandatory; if you don't have every script yourself, you need to have the ability to reverse engineer/discover the DDL changes between the databases. a tool like Redgates SQL Compare can help you do that.
this is what i would typically do:
1. Compare the two database, or get the appropriate Change Scripts from source control so they can be applied.
2. Backup the database on production.
3. Restore that backup on Dev.
4. Apply the schema changes(DDL) from step 1.
then the developers would have the latest data, but with their prototype schema changes in place.
I know i built my own crappy little tool for schema changes exactly for this reason; it's a lot of work, especially if the developers don't submit scripts until the very end;
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!