Sql-server – How to execute alter database commands from DbUp scripts

migrationsql serversql-server-2016

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.

Best Answer

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.

Related Question