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?

• Gareth Lyons: Transactions can span batches (GO separators), but TRY-CATCH blocks can't.
• David Browne - Microsoft: Why not just use SSDT to generate a schema diff script? learn.microsoft.com/en-us/sql/ssdt/…
• Seafish: @DavidBrowne-Microsoft A portion of the migration script will be generated by SSDT but we have some more specific rename/data transform operations that also need to be performed that need to be delicately ordered/handled. The idea would be to concatenate the SSDT output to our custom script and have one big transaction, but it's looking like it won't be that easy
• Seafish: Summary of the answers: put statements requiring separate batches outside try/catch and manually verify their success, run without try/catches but manually check errors after each batch, or use sqlcmd with -b to cancel all batches if one fails.

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

• RDFozz: I've had to apply DB scripts to update a vendor's database on numerous occasions over the past 13 years, and this has always been the process: take the app offline; take a full backup; run the script; confirm all is good. The script has involved new/updated functions and procedures, and thus has never been a single transaction. However, by adding appropriate notes via PRINT or some sort of logging, you should be able to track where the script dies if there's an error, and pick up from there.

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

• Seafish: Shouldn't that be if @@trancount > 0?
• irimias: Yes indeed, I fixed the typo.