Re: Move data from production schema to development schema which has some modifications
Date: Tue, 19 Jan 2010 10:43:40 -0800 (PST)
Message-ID: <dd85b305-e1e7-493d-8060-7e0fdc7533d8_at_v25g2000yqk.googlegroups.com>
On Jan 19, 4:49 am, Naga <nagaraj.mam..._at_gmail.com> wrote:
> Hi Tom/Group,
>
> Hi All,
>
> I am new to DB related development, I am currently asked to come up
> with a solution to write a script or use a tool to map tables between
> two schemas and copy data from one DB to another
> A simple scenario would be, we have our production DB with the current
> schema, we have a change in structure of development DB or just change
> names of some columns in some tables.
> the requirement is to have a script or a tool which can backup the
> production data and copy them to the new structure of development DB
>
> Is there any way i can export data from production system, and import
> them in the modified schema(which has some column names changed or
> tables added with null value).
>
> can anyone help me in this regard as I am totally blank on how to
> proceed with this.
>
> Thanks in Advance,
>
> Nagaraj Mamedi
>
> Eagerly awaiting for the solution !!!!!!!!!!!!
There isn't just one solution. Gazzag and Mark have show a couple, but this is not really generalizable. The data part is fairly straightforward, but you have to also coordinate a testing database, integration and application executables. The latter is usually done with a source code control system, the issue is coordinating that with the data.
The problem with generalizing it lies in the combinations of what can happen; adding columns, dropping columns, indexes, redefining foreign keys, with various quantities of data and changing business rules. Once things are in production, a tool needs to be able to do a lot more. In the past, there were fewer options for, say, invalidating or dropping columns, so I've seen some enterprise systems with lots of decremented columns, confusing the heck out of maintenance or customization programmers. It's not simple after several generations.
Sometimes the solution is create table as select from the production table, followed by populating the new fields, then renaming, indexing, and recalculating statistics. In some cases, statistics need to be fixed after verification, or even played with then fixed.
The ERP I work on has a long involved procedure for upgrades, that includes determining if there are custom-added columns or indexes. Naturally, there are conflicts that need to be resolved as the base product adds functionality that was previously custom. In the end, this often means some very strange data distributions as columns are added, data moves around because it no longer fits in blocks and so forth. So you need to be able to determine if that is an issue. Even a simple export/import without changing anything can bite you in some situations.
Think on this: Development and testing are going to be ahead of production. So you want to be able to script importing the data, changing the metadata, then modifying the data as necessary. The less manual labor, the less chance for error, and the more accurate you can be when you apply it to production. Make the developers make the scripts, then evaluate them closely.
Rule of thumb: test, test, test.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/jan/19/siren-near-socal-nuke-plant-was-false-alarm/Received on Tue Jan 19 2010 - 12:43:40 CST