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

Agriculturist

msdn.microsoft.com/en-us/library/… – Dayle

Answer 1

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

Agriculturist

I realise this is the correct answer, but how on earth did you discover that 0 works when the parameter is normally a string?! – Apperception

Just trial and error really after trying everything I could think of to trick it into working :) – Agriculturist

Does this create an exact copy of the database? Including the table contents? – Dempster

@Multitut: no it'll only do the structure. – Assisi

@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! – Numeral

How can we script the Seed Data? :) – Propertied

Shows an empty script for me. – Prussiate

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. – Luo

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: learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/… – Ternopol

using a "0" will revert all the migrations first. Not a good idea if you have data – Dziggetai

really dumb question but where I can find the script after generating it? – Scarletscarlett

Answer 2

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.

Pardo

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 – Dziggetai

You can omit the timestamp in the migration names and it'll still work. – Cannae

Answer 3

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).

Charming

Answer 4

Case 01: The following generates a SQL script from a blank database to the latest migration.

In .Net Cli type the following.

dotnet ef migrations script

If you want to do the same in visual studio package manager console. Type the following

Script-Migration

---

Case 02: The following generates a SQL script from the given migration to the latest migration.

In .Net Cli

dotnet ef migrations script AddNewTables

In visual studio package manager console

Script-Migration AddNewTables

---

Case 03: The following generates a SQL script from the specified from migration to the specified to migration.

In .Net Cli

dotnet ef migrations script AddNewTables AddAuditTable

In visual studio package manager console

Script-Migration AddNewTables AddAuditTable

---

For more details. Please refer to the link.

Intertidal