August 15, 2006 at 9:38 am
We have a faily large database, supporting a financial application. When a user purchases the application, we deploy the database out on their local server and push db updates via change scripts.
One the main problems we face is when a database on the client site get out of synch (for whatever reason) with the master we have here at the dev shop.
Dropping tables is a problem, b/c all constraints are not always dropped from a table before running the DROP TABLE... command. The results in the script bombing and a broken upgrade.
Does anyone have any experience with how to make dropping all constraints on a table as foolproof as possible?
thanks!
August 15, 2006 at 3:26 pm
August 16, 2006 at 4:18 am
some things to think about (if you haven't already).
What about data in tables?
Re-creating constraints.
You 'could' consider the following process (of the top of my head, so work through it to add other necessary steps):
1. Create Temp Table based on NEW DDL
2. Insert data from LIVE table into TEMP TABLE
3. Drop Live table constraints
4. DROP LIVE TABLE
5. Rename TEMP table to LIVE TABLE
6. Create Constraints on LIVE table (this is the NEW DDL live table).
some things to consider - large tables could take a while! Some data type changes won't handle this - for example if customers have alphanumeric data in a varchar, and you are changing it to INT, etc. etc.
HTH
August 16, 2006 at 5:03 pm
Hi there,
Where I work we have database masters as well as the testing/dev environment. The database masters allow us to check that we are not going to cause database errors during an upgrade of a client site.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply