I'm trying to come up with a database script that will be used to migrate an earlier version of an application database to a later version. The differences are quite extensive, and accomplishing the migration will involve creating new tables, renaming tables, adding functions, adding views that have calculated columns that depend on those functions, etc. After the script is done, I will hand it to somebody else to run on the target environment.
I'd like to be able to run the entire migration in a single transaction so that if something goes wrong it will be simple to roll back.
The problem I'm running into is that when when the script tries to
CREATE FUNCTION, it complains that:
SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.
I've done some research and found that SSMS allows the use of the
GO keyword to separate batches, but every time I try to wrap the entire script in a
BEGIN TRANSACTION and then include a
GO to separate the create function call into its own batch, I get an error:
Incorrect syntax near 'GO'.
I'm not sure why, since answers like this one seem to work with GO statements in there.
I'm also making an assumption (maybe incorrectly) that the person I give the script to will be using SSMS.
I also found a couple articles that mentioned using
SET XACT_ABORT ON;, which I've tried outside of the transaction at the very beginning.
So my script looks something like this:
SET XACT_ABORT ON GO BEGIN TRY BEGIN TRANSACTION -- Create some tables GO -- Error CREATE FUNCTION [...] GO -- Error -- Create some views that rely on the above function COMMIT TRANSACTION END TRY -- With the GOs in there, this line also gives an error: Incorrect syntax near 'TRY'. Expecting CONVERSATION. BEGIN CATCH -- Error handling, including transaction rollback END CATCH
Any recommendations on how to migrate a SQL database using multiple batches but one transaction in a single script?