SQL Server – Handling Multiple Batches in Migration Script

sql serversql-server-2016ssms

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?

Best Answer

I guess it would be easier to simply restrict the access to the database then take a backup. If there is a problem, simply restore the backup.

That way, you won't have to worry about that kind of error.

Otherwise, put the Try Inside the global named transaction. In the catch, rollback the named transaction. ex: 

BEGIN TRAN Glob

CREATE FUNCTION Test()

...

go

begin 

"Check if function was created correctly"

If not: ROLLBACK TRAN Glob

end 

-- For other treatment

Begin try

.. Code..

End Try

begin catch

rollback tran glob

end catch

go

That should work too

Related Question