Re: Database Refresh Procedure

From: Fred Pierce <fpierce_at_avialantic.com>
Date: Tue, 20 Aug 2002 13:24:11 GMT
Message-ID: <3D624439.2000008_at_avialantic.com>


John Bossert wrote:

> I need to frequently refresh my development and staging databases with
> production data. If anyone has a script to do this, I'd appreciate a
> pointer.
>
> Alternatively, I believe I could just write a script that:
>
> 1) disables constraints (on target DB)
> 2) truncates the tables (on target DB)
> 3) imports the source data (to target DB, from source DB)
> 4) enables constraints (on target DB)
>
> Am I leaving out anything obvious? Thanks.
>

If the target databases are identical to the source then Rauf's cloning suggestion would work, but if not then export/import would have to be used. Add triggers to the list of things to be disabled.

All this is assuming your database is small enough to make exp/imp practical. It can get pretty time-consuming. Dropping indexes helps on that. There's a white paper out there somewhere on improving import efficiency. In short, you have to fiddle and read, especially the exp/imp utility docs, to create the solution that fits your circumstances. Later versions also have a "where" clause capability so you can export a subset of your data if it gets too big.

fdp



Fred Pierce (DNRC)- fpierce_at_avialantic.com Mid-Atlantic Aviation on the Web - http://www.avialantic.com Planes, Trains, & Automobiles Transporation Show Sept. 21-22 2002 - www.maam.org **
*** World Airshow News - www.worldairshownews.com **
Received on Tue Aug 20 2002 - 15:24:11 CEST

Original text of this message