Maybe you did not know this, but the recommended way to deploy Entity Framework Core migrations to a production database is by generating SQL scripts!

To generate a SQL script containing migrations after a given named migration, you can run:

dotnet ef migrations script AddNewTables > script.sql

You (or your DBA or a script in your deployment pipeline) can then run the script using sqlcmd:

sqlcmd -S myserver -d mydb -i script.sql -E

Notice that the sqlcmd option switches are case sensitive!

-S is the name of the database server or instance

-d is the name of the database to run the script against

-i is the path and filename of the script to execute

-E means "use trusted connection", you and also use SQL username and password with -U and -P

Running this script you may run into the following error message:

CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

This is due to the fact that EF likes to create filtered indexes, in fact any unique SQL Server index will have a filter similar to this:

CREATE UNIQUE INDEX [IX_Products_BarCode] ON [Products] ([BarCode]) WHERE [BarCode] IS NOT NULL;

To fix the error message, add the -I switch to the command, this causes the sqlcmd session to use SET QUOTED_IDENTIFIER ON.

sqlcmd -S myserver -d mydb -i script.sql -E -I

Comments or questions for this blog post?