How to transactionally update a DB Schema?

I am trying to build a database schema updater (in C#) that can be used to
upgrade the various databases associated with my company's productized and
custom applications.
I've come up with what I think is an effective way of storing, specifying,
and executing the required T-SQL scripts to change the database from one
schema version to a new one, but I am having trouble reconciling how to deal
with potential errors while upgrading.
Is there a way to transactionally perform various "CREATE/ALTER
TABLE/PROCEDURE/TRIGGER/VIEW" and "INSERT/UPDATE/DELETE" statements so that
if one fails that I can rollback to before the upgrade started? If SQL
Server doesn't provide this functionality, what would be a "best practice"
to handle these types of errors?
Thanks,
Matthew Belk
BizSpeed, Inc.You can execute DDL as part of an explicit transaction so that you can
rollback in the case of an error. However , I personally would backup the
database prior the upgrade and restore if an error is encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matthew Belk" <mbelk@.bizspeed.com> wrote in message
news:uqNW53WHEHA.3564@.TK2MSFTNGP09.phx.gbl...
> I am trying to build a database schema updater (in C#) that can be used to
> upgrade the various databases associated with my company's productized and
> custom applications.
> I've come up with what I think is an effective way of storing, specifying,
> and executing the required T-SQL scripts to change the database from one
> schema version to a new one, but I am having trouble reconciling how to
deal
> with potential errors while upgrading.
> Is there a way to transactionally perform various "CREATE/ALTER
> TABLE/PROCEDURE/TRIGGER/VIEW" and "INSERT/UPDATE/DELETE" statements so
that
> if one fails that I can rollback to before the upgrade started? If SQL
> Server doesn't provide this functionality, what would be a "best practice"
> to handle these types of errors?
> Thanks,
> Matthew Belk
> BizSpeed, Inc.
>|||I'm doing that, too, if something catastrophic were to happen, but in the
case of upgrading through multiple versions, I would like to commit the
transaction for each version as I successfully run all the scripts for that
version.
Thanks,
Matthew
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ezC50FXHEHA.4088@.TK2MSFTNGP10.phx.gbl...
> You can execute DDL as part of an explicit transaction so that you can
> rollback in the case of an error. However , I personally would backup the
> database prior the upgrade and restore if an error is encountered.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Matthew Belk" <mbelk@.bizspeed.com> wrote in message
> news:uqNW53WHEHA.3564@.TK2MSFTNGP09.phx.gbl...
to
and
specifying,
> deal
> that
practice"
>