Skip to content

Update SQL Database using EF Core and Azure DevOps

If you write an app you probably need a way to store data. If you do it using a SQL Database (eg. SQL-Server) you need a way to change your Database-Schema sooner then later.

There are several options on how to do this in the Microsoft eco-system. Here I describe a very easy and simple way on how I do it for my personal project over at


First of all my app is a ASP.Net Core MVC 2.2 app using EF Core 2.2 with SQL-Server. More precisely with Azure SQL-Server. The application is hosted on Azure AppService. I use Azure DevOps to fully automate my build and release pipeline.

My goal was to automatically run the schema-migration as part of my automated deployment. The solution should be straight forward and transparent. It also should not need much of custom code.


I’m using EF Core’s Migrations to add migrations as soon as my schema needs to change. Normally I commit this migration together with my modified ORM model classes. Migrations are generated and added to the project using the following command:

dotnet ef migrations add

These migrations can be run on the command line using the EF CLI command:

dotnet ef database update

All pretty standard so far.

Instead of running the above commands when deploying and hope they will succeed I like to do it more SQL-like and more transparent. Therefore I maintain one single SQL-Script that updates any schema-version to the latest version.

Such an SQL-Script can easily be tested locally or on test-servers and with different datasets. If something goes wrong on production system’s migration we can fix the SQL-Script manually and are good without building a new software package.

Lucky me EF Core has this feature built into. I use a command line like this to generate the SQL-Script:

dotnet ef migrations script -i -o ../../sql/update_to_latest.sql

The generated SQL-Script contains the IF-Statements to check which migrations needs to be applied and which are already. It also takes care of updating the migration-history-table.

My build-job copies the sql/update_to_latest.sqlscript into the build artifact.

For my release-jobs I’ve created a Task Group called “Migate DB to latest”. I used the task group so I can re-use this step on all my environments (actually stage and prod). The Task Group contains one step of type Azure SQL Database deployment and executes the above SQL-Script.

Screenshot 2019-07-08 at 22.35.04.png

Finally I’ve added this Task-Group to each environments release-steps.

Screenshot 2019-07-08 at 22.41.37.png

Important: To get the connection to your SQL-Server working you have to add the connection in Azure Service Connections.

You can find my real-world docs over at GitHub.

4 thoughts on “Update SQL Database using EF Core and Azure DevOps Leave a comment

  1. As much as I like deploying code using the above method (did I mention how ridiculously easy it is), there are those that want finer grained control over how their database scripts are created and run. Maybe all DB scripts have to be reviewed by DBA’s. Or maybe they want complete control over what those scripts do. Or maybe they want to take backups and do other stuff before applying schema changes. Whatever the reason, for those that fall in this camp, you can have the build create migration scripts for you via the build. And then when it’s time to deploy, the deployment can run those migration scripts.


    • That’s up to you and your requirements. Please note that that method generates the script and I check this into my source-control. The deployment will execute this script on the database. One can review the script etc. No binaries required for deployment.


  2. shouldn’t this be an inline script with a transaction wrapper in case of problems?

    SET XACT_ABORT ON   — auto rollback
    BEGIN TRANSACTION   — begin tran and inc TRANCOUNT
    :r $(Build.StagingDirectory)\migrations.sql  — load contents of script and insert it here
    IF @@TRANCOUNT > 0 COMMIT  — commit if no errors



    • I preferred the script in the build artifacts to it will move from one to the other environment as I deploy the environments. It’s part of the software package and deployment.

      The scripts use SqlCmd and their „GO“ statements. So each migration step is executed in its own transaction. If a migration step fail for what ever reason the previous steps stay applied. One can fix the data or the failing script and continue the migration from where it previously failed.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: