On Wed, Jan 19, 2011 at 12:12 AM, French, Martin <frenchm@cromwell.co.uk> wrote:
> Ok, you say that you cannot drop and recreate, so you need to do this via
> alter statements only? That’s obviously going to complicate matters, as a
> straight dump, drop, recreate, restore would be the fastest and by far
> simplest method.
>
>
>
> So, Ideally, you’ll need to do a table def comparison over the two
> databases, and generate the necessary sql to amend the tables in test
> accordingly?
Sorry but this is exactly backwards from good procedure. What you do
it have your developers checkin the SQL code they used to alter the
tables / add rows in the test database, and you apply that to testing
/ QA / staging / production. Any other way is a recipe for both
disaster and DBA burnout.
To make it easier, you can always turn on ddl logging on the
developer's databases and then just trawl the logs for what they did.
Still easier than trying to compare schemas and figure out what's
different and how to write the SQL to make it happen.