Multiple Batches in SQL Server Migration Script

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

try...catch will not work with go inside.

You could add instead a commit / rollback block after each statement.

Something like:

begin tran

-- here statement #1
if @@error <> 0
begin
    rollback tran
    return
end 
-- here statement #2
if @@error <> 0
begin
    rollback tran
    return
end 

if @@trancount > 0
    commit tran

Sure, that's not very nice nor convenient if you have a lot of statements...

Or, same idea, based on what tool SQL Examiner is generating for DB synchronization:

set nocount on
set noexec off
set arithabort on
set xact_abort on
set transaction isolation level serializable
go
begin tran
go
--step 1: I want to create a table_1
create table [dbo].[table_1] ........
go
if @@error <> 0 and @@trancount > 0 
begin 
    print 'step 1 is completed with errors' 
    rollback tran 
end
go
if @@trancount = 0 
begin 
    print 'step 1 is completed with errors' 
    set noexec on 
end
go
--step 2: I want to create a table_2
create table [dbo].[table_2] ........
go
if @@error <> 0 and @@trancount > 0 
begin 
    print 'step 2 is completed with errors' 
    rollback tran 
end
go
if @@trancount = 0 
begin 
    print 'step 2 is completed with errors' 
    set noexec on 
end
go

--end
if @@trancount > 0 
begin 
    commit tran 
    print 'everything is ok' 
end
go
set noexec off
go