How to execute alter database commands from DbUp scripts?

Question

I am using DbUp to maintain and upgrade my SQL Server 2016 databases. Typical SQL scripts that I have executed so far included tasks like creating tables, creating or altering stored procedures, etc.

I am going to start using Memory Optimised Tables, and need to run ALTER DATABASE commands to create the necessary filegroups in the existing databases. I tried running a dynamic SQL script that builds and runs ALTER DATABASE commands containing the database name that the script is currently running for, but since DbUp uses transactional execution, it seems that ALTER statements are not allowed and I am getting this error:

Script block number: 2; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Any ideas how I can run ALTER DATABASE commands via DbUp, or is there an alternative way how to handle this case?

Thanks in advance.

• RLF: I believe the actual problem is because you are running several ALTER statements in a single transaction. Try running each ALTER in its own transaction.
• Scott Hodgin - Retired: Another option 'might' be to use xp_cmdshell (in a script file) to kick off SQLCMD to run your ALTER - exec xp_cmdshell 'sqlcmd -S . -Q "ALTER DATABASE [Test] SET RECOVERY SIMPLE WITH NO_WAIT" -E'
• Aaron Bertrand: Or just change your scripts so that the database alters are outside of the transaction for tables/stored procedures. If you add a filegroup to a database to support In-Memory OLTP, then add physical files to the hard disk, and then some procedure doesn't get created because of a syntax error, do you really want to roll back the file/filegroup changes? Do you think that's even possible?
• ChrisS: @RLF - just tried that, but it didn't work. Executing only one ALTER statement in a script produced the same error.
• ChrisS: @ScottHodgin - that's a possibility, i'll try some prototypes and will see if it works. Thanks.

Answer 1

This is DbUp's default behavior; nothing you can do on the SQL Server side. There's a similar discussion on DbUp's github issues section with workaround that may work for you so do check it out. I don't think there's a DbUp tag on SE so if those workarounds don't work for you, try posting on that page or their discussion group.