Generate full SQL script from EF 5 Code First Migrations

Question

How do I use Entity Framework 5 Code First Migrations to create a full database script from the initial (empty) state to the latest migration?

The blog post at MSDN Blog suggests to do this, but it seems to create an empty script:

Update-Database -Script -SourceMigration: $InitialDatabase

https://msdn.microsoft.com/en-us/library/jj591621(v=vs.113).aspx#Generating-Idempotent-Scripts-EF6-onwards — Nick Spreitzer, Sep 26 '17 at 00:03

score 316 · Accepted Answer · answered Dec 18 '12 at 18:44

316

The API appears to have changed (or at least, it doesn't work for me).

Running the following in the Package Manager Console works as expected:

Update-Database -Script -SourceMigration:0

answered Dec 18 '12 at 18:44

Matt Wilson

8,159
8
33
55

17

I realise this is the correct answer, but how on earth did you discover that 0 works when the parameter is normally a string?! – Dave R Mar 10 '14 at 16:33
31

Just trial and error really after trying everything I could think of to trick it into working :) – Matt Wilson Mar 10 '14 at 21:20
1

Does this create an exact copy of the database? Including the table contents? – Multitut Mar 20 '15 at 14:36
2

@Multitut: no it'll only do the structure. – Martin Clarke Sep 10 '15 at 08:00
1

@DaveR there's actually a special `$InitialDatabase` variable that symbolizes that "special initial migration". But here's the trick: `$InitialDatabase` is just an alias for `0` constant! – ForNeVeR Oct 10 '16 at 05:13
How can we script the Seed Data? :) – Kbdavis07 Dec 20 '16 at 04:35
1

Shows an empty script for me. – John Nov 29 '17 at 13:03
Note: The generated script checks in __MigrationHistory to see if the migrations have already been applied. It does this purely on the name of the migration (does not actually compare schemas). Therefore if the reason you want a full script is to be able to delete the tables and start again you must also delete any items in __MigrationHistory for the context you're using. – Simon_Weaver Dec 11 '17 at 08:58
10

Just in case someone is looking how to do this in EfCore and ended up here like me, the command is: `dotnet ef migrations script`. More on documentation: https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/#generating-a-sql-script – Rafael Miceli Mar 06 '18 at 19:28
using a "0" will revert all the migrations first. Not a good idea if you have data – Nick Turner Jul 20 '20 at 21:16
really dumb question but where I can find the script after generating it? – DavidY Dec 03 '21 at 13:01

score 55 · Answer 2 · edited Jun 20 '20 at 09:12

For anyone using entity framework core ending up here. This is how you do it.

# Powershell / Package manager console
Script-Migration

# Cli 
dotnet ef migrations script

You can use the -From and -To parameter to generate an update script to update a database to a specific version.

Script-Migration -From 20190101011200_Initial-Migration -To 20190101021200_Migration-2

https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/#generate-sql-scripts

There are several options to this command.

The from migration should be the last migration applied to the database before running the script. If no migrations have been applied, specify 0 (this is the default).

The to migration is the last migration that will be applied to the database after running the script. This defaults to the last migration in your project.

An idempotent script can optionally be generated. This script only applies migrations if they haven't already been applied to the database. This is useful if you don't exactly know what the last migration applied to the database was or if you are deploying to multiple databases that may each be at a different migration.

This works to a point. Once you start changing column names it will start throwing errors, creating a DacPac is a better solution. Especially when you start using Pipelines in CI/CD — Nick Turner, Jul 20 '20 at 21:17
You can omit the timestamp in the migration names and it'll still work. — Ε Г И І И О, Jan 27 '21 at 13:43

score 10 · Answer 3 · edited Aug 28 '18 at 14:00

To add to Matt wilson's answer I had a bunch of code-first entity classes but no database as I hadn't taken a backup. So I did the following on my Entity Framework project:

Open Package Manager console in Visual Studio and type the following:

Enable-Migrations

Add-Migration

Give your migration a name such as 'Initial' and then create the migration. Finally type the following:

Update-Database

Update-Database -Script -SourceMigration:0

The final command will create your database tables from your entity classes (provided your entity classes are well formed).

Generate full SQL script from EF 5 Code First Migrations

3 Answers3

Linked