Re: Refreshing data on a test database

Neil, Truby wrote: > > Our users have a requirement for a test database, with the data regularly > refreshed from production. We achieve this by dbexporting the production > database, dropping the test one then using dbimport to re-create it. > If your physical layouts are the same, why not restore a production backup to the test system. That's how we do it. It's a LOT faster than dbexport/dbimport. > The users are beginging to have their own stored procedures and triggers > developed, and do not want to lose these. To continue to provide them with a > data refresh whilst preserving other database items I have thought of the > following methods: > > 1. Unload each table from production, cleardown from then load into the > corresponding table on the test database. > > 2. Unload the procedures and triggers on the test database, use the > dbexport-drop-dbimport as at present, then re-load the procedures and triggers. > > Neither seems well-suited to a regular, scripted process. Any other ideas? We have the same issue with indices and table alterations. As DBA, I enact these changes for our other developers. When I restore test from production, I run a script that goes to my $SCHEMA directory and runs the designated SQL commands. Another option might be to do a diff between your production databases and test databases and apply the differences to your test system, but that sounds a bit tricky. Best wishes, John Carlson WH Smith, Inc These views are all MINE!!!! < muhahahahahahahaha >