24

I'm using EF migrations to track changes to our EF code-first DB model. Now I need to generate one SQL-Script for each migration, so that I can pass that set of scripts to the DBA.

I was able to generate SQL scripts with Update-Database -Script ...

However, I'd like to automate this. I hoped that the -Script switch would accept a parameter that specifies where to write the SQL to, but this is not the case. Also, output redirection does not work, because the SQL script is not written to STDOUT, but to a temporary file. I see no way of getting hold of that temp file from a script.

Any ideas how to automate the Migrations -> SQL Script generation? Maybe there is some magic powershell trickery that I'm not aware of?

EDIT: By the way, using migrate.exe or any other "migration-aware" approach is not an option, delivering SQL scripts is a must.

theDmi
  • 17,546
  • 6
  • 71
  • 138
  • "Now I need to generate one SQL-Script for each migration, so that I can pass that set of scripts to the DBA." - not that this answers your question, but wouldn't a single script that includes all the necessary migrations suffice for your DBA? – PinnyM Jan 14 '13 at 19:28
  • No it wouldn't, because we need to support incremental DB updates of productive systems. With one script, the DBA would need to find out which changes to apply to the existing DB. – theDmi Jan 14 '13 at 20:58

1 Answers1

32

Finally I found a solution. What I wasn't aware of is that it is possible to generate SQL scripts from C# code as follows: 

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;

var migrator = new DbMigrator(new Configuration());
var scriptor = new MigratorScriptingDecorator(migrator);
var sql = scriptor.ScriptUpdate("Name-Of-Source-Migration", "Name-Of-Target-Migration");

Together with migrator.GetLocalMigrations() you have full control over the granularity of the generated scripts.

theDmi
  • 17,546
  • 6
  • 71
  • 138
  • 1
    Thank you for the automation solution. I going to integrate it into our CI asap – Mando Mar 14 '16 at 22:31
  • 1
    Hey man, appreciate the self answer here. I'm in the exact same situation. – Jono May 22 '17 at 10:50
  • Thank you so much! This helped me tremendously with crunching down a bunch of migrations into a flat 'baseline' to cut down on build bloat. Thanks again! – Andy_Vulhop Jun 28 '17 at 15:32