Multiple Batches in SQL Server Migration Script

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?

The problem is really just about error handling, as SSMS has no way to handle errors across batches when you run the script in an SSMS query window (ether normal or SQLCMD mode). So your transaction might abort, and SSMS would run the next batch.

If you run the script with SQLCMD you can start a transaction in the first batch, and use the -b switch. Any error will abort the script and roll back your transaction.

EG

use tempdb
go
begin transaction
-- be sure to run with sqlcmd -b
-- eg c:> sqlcmd -b -i c:\deploy\thisscript.sql
go

--. . . any number of batches here

go

commit transaction

go

Of course you must ensure that your script does not commit or rollback the transaction, catch errors, or contain any statements that can't be run in a transaction (like adding files to a database).

You can similarly burst the batches yourself in .NET or PowerShell, start a transaction, and run each batch separately, commiting only if all batches ran without error.

Another good way to handle this whole scenario (if you can do the upgrade during an outage) is with a database snapshot. In case of a failure, restoring from a database snapshot allows a quick rollback of all the upgrade actions without the need of a transaction.

StackExchange Database Administrators Q#194515, answer score: 5