Robust database with easy rollback capability...

We want to improve our database upgrade so that upon failure, customers
can more easily get back to their previous version while we look into
the problem. We sell a packaged product.
Currently, we export data into a homegrown format, create a new
database, and then import into the new database. We'd like to rid
ourselves of this intermediate, dangerous step (homegrown export
format). The reason it exists is that we used to support Access (Jet).
I'd like to do 'in-place' upgrades, more or less; leave the database
where it is, and tweak schema or data as needed. It seems a lot safer
and would certainly be much quicker.
I wanted to get ideas...what are some techniques that perhaps some of
you use?
Some of my thoughts:
* detach the old database, make a copy, attach to the copy. Upgrade the
copy, but on failure, reattach the original.
* Use DTS to move data from the old to the new. Don't switch to the new
until/unless it looks good.
* Put a transaction frame around the whole upgrade process and roll back
if it fails. Somehow, this sounds a little scary to me, though. Should it?
Other ideas? I'd love to hear more.
One of the goals is to not have the customer do anything manually in all
of this.Do you know for sure that your customers are backing up their database and
that they are not making custom changes to the table structures? If so, then
you could feel more confident about deploying data change scripts.When you
make changes to a table design in Enterprise Manager, but don't click the
[Save] button, you can click the [Save Change Script] button to see
what
T-SQL code EM is using to make the changes. It includes all the scripting
needed to insert / select existing data from a temporary table and also
wraps everything in a BEGIN.. END.. transaction. You could deploy the script
as is, or customize as needed.
"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eqMNmLXBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should
it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.|||This is how I always do it:
I create a change script using the database change management software DB
Ghost (http://www.dbghost.com).
I get all users off the database I'm about to change.
I back up the database I'm about to change.
I run the script.
I run the tests to verify that all is well.
I back up the database again so I have a backup before the changes and a
backup after the changes.
I open the database to all users.
"Mike Jones" wrote:

> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should i
t?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
>|||This is harder to pull off with packaged software. We must send out a
setup program that users can run easily.
[vbcol=seagreen]
> This is how I always do it:
> I create a change script using the database change management software DB
> Ghost (http://www.dbghost.com).
> I get all users off the database I'm about to change.
> I back up the database I'm about to change.
> I run the script.
> I run the tests to verify that all is well.
> I back up the database again so I have a backup before the changes and a
> backup after the changes.
> I open the database to all users.
> "Mike Jones" wrote:
>