best way of making changes to a production database

HI all,
We have three verions of our database, one that is in continual development,
do'nt as why, it would take me too long to explain, but they are valid.
ANyway, we have a developer version, which we develop on test changes, crash
etc etc.
Then we have a test version where people can test the new changes to the
application and database,
Then we have the live version where the live data is, obviously.
What is the best practice to move changes from one database to another
wthout affecting or deleting the current data.
The easiest way would be to script the database, drop it then run the
script, not good because it looses all data. This is ok for the test
database, as we don't care what we loose there, but can not do that for the
live data.
Thanks
RobertThe way we do it, and I would've thought this is standard practice, is
to script the changes to the database, including any structural
changes, stored procedures, triggers etc, and apply the script to each
database.
I don't see how you could script the entire database and be expected to
run it against a database and retain data.
Kris|||We use SQL Compare from Red-Gate Software to generate change scripts.
However, I must say that my last major roll-out didn't go as planned
because I didn't review the script it generated as carefully as I
should. On the whole, though, it's a great tool for making production
changes.
Stu|||If you're willing to put the procedures in place, then in my opinion
the safest way to ensure that things getting properly transferred is to
only make changes to your databases through SQL scripts (don't add
columns etc through EM). Then save every script you run, then to roll
out to the next database, just re-run all the scripts. However this is
just my opinion. It does mean that you'd need to run every script, even
the ones that went wrong, just to make sure you achieved the same end
result.
Will|||Start with identical Test and Production schema. Promote changes from
Development to Test using only scripts (preferably source-controlled).
Before you go live, apply the scripts to a copy of Production to make sure
that all goes as expected.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:ugAZAtqaGHA.1352@.TK2MSFTNGP05.phx.gbl...
> HI all,
> We have three verions of our database, one that is in continual
> development,
> do'nt as why, it would take me too long to explain, but they are valid.
> ANyway, we have a developer version, which we develop on test changes,
> crash
> etc etc.
> Then we have a test version where people can test the new changes to the
> application and database,
> Then we have the live version where the live data is, obviously.
> What is the best practice to move changes from one database to another
> wthout affecting or deleting the current data.
> The easiest way would be to script the database, drop it then run the
> script, not good because it looses all data. This is ok for the test
> database, as we don't care what we loose there, but can not do that for
> the
> live data.
> Thanks
> Robert
>