Rolling back table updates

[riksweeney]

Hi,

I'm updating a client's table structure and have a copy of their database structure and have made an update script based upon our latest schema by saving the generated scripts from SQL Server. I've dumped them all in one big file for convenience. What I need to do is be able to roll everything back if something stupid happens during the update. Currently the script looks something like this:

CREATE table
CREATE table
CREATE table
CREATE table
CREATE table

ALTER table
ALTER table
ALTER table
ALTER table
ALTER table

ADD PRIMARY KEY
ADD PRIMARY KEY
ADD PRIMARY KEY

etc.

If I add a BEGIN TRANSACTION to the top of the file will this rollback everything if there is an error or do I need to do something else aswell?

Thanks

Richard

 

[RiazKhanmohamed]

it will only rollback if you finalise it. however you have to finalise it in any situation:

'commit transaction'

if all successful

'rollback transaction'

in event of a failure

these can be run as separate commands, not as part of the list of processes required.

If you don't commit on success, i can't guarantee your changes will stay.