How to Cleanup & generate fresh migration for existing DB?

Question

We wish to get rid of 100s of migration classes as DB schema in production is final.

Here are the steps I followed:

Delete Migrations folder.
Add-Migration -??

What command line switches, could help us?

EDIT:

If all goes well Up() method of migration should be empty right? For example following is wrong generation on Add-Migration. Because if we execute the project we will get duplicate table errors.

public partial class Sanity : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.AccountPreferences",
            c => new
            {
                AccountID = c.Guid(nullable: false),
            }
            .... for 1000s of tables
      }
}

A clean migration would be something: when trying Add-Migration on subsequent changes, should not be getting any error.

Unable to generate an explicit migration because the following explicit migrations are pending: [201712281054591_Sanity]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

As you can see if we happen to execute Update-Database will get table already exist error.

Are we forced to always retains all migration copies?

score 2 · Accepted Answer · answered Dec 28 '17 at 14:20

See if this can help:

MVC3 and Code First Migrations - "model backing the 'blah' context has changed since the database was created"

Entity framework code first - how to run Update-Database for production database

How to delete and recreate from scratch an existing EF Code First database

note:
I'm writing this from memory, if you have issues let me know and I'll recheck exactly.
Also, my knowledge on this is from slightly older versions of EF as I haven't done much work there recently, but I doubt much has changed.

From what I can tell, if you want to...
a) keep the db,
b) clean your project migrations,
c) have the 2 'match', be in sync:

do the following:
- Remove the migration folder (your project)
- Run Add-Migration Initial - then should add one migration
- caution: it is safe but do backup, change connection string etc. before the next step
- Run Update-Database -Script - that doesn't update the db but creates the SQL script, including the migration table
- find the INSERT INTO [__MigrationHistory] records, just run those (on your db), insert them into the database

...then test with Add-Migration again, to see if it is going to make anything, should yield no new migrations, empty one.

Please read through the first link above and adjust approach as needed.

I'm sure there might be easier, shorter ways to do this (via PM console) but unaware of it at the moment.

thanks `find the INSERT INTO [__MigrationHistory] records, just run those (on your db)` should do the trick. Lmc and revert back. — Abhijeet, Dec 28 '17 at 14:25
@Abhijeet I'm outside but remembered, if you run Add-Migration against the full db it won't do a thing. You need to run it against the empty db, or just change the connection to something, just to trigger it to think it has to regeneration everything from scratch, I'll follow up later if needed. — NSGaga-mostly-inactive, Dec 28 '17 at 14:35
Got it can do it against `Empty DB` as well however, based on my experience DB with 1000s of Migratinos executed will not be same as Fresh DB even though similar `Fluent APi` — Abhijeet, Jan 02 '18 at 12:57
@Abhijeet what you're saying doesn't make sense, it either works or doesn't, this is the official way of dealing with production scenarios (and I did quite a few). 1 migration or 1000s (1000s? honestly:) makes no difference, the end result, db structure is the same, the only thing to worry is matching migration histories, and that should be solved following the above (roughly, I gave you a blueprint, the rest is up to you to solve it for your own case). And in SO spirit if posts help you upvote, mark as answer, downvote if you wish, but people spend time trying to help you. — NSGaga-mostly-inactive, Jan 02 '18 at 17:08

score 0 · Answer 2 · answered Dec 27 '17 at 18:08

0

Open your database.

Clear table __MigrationHistory

Remove migrations in the folder

Run Add-Migration MigrationName

answered Dec 27 '17 at 18:08

dropoutcoder

2,627
2
14
32

thanks but this generates fresh `CreateTable Scripts` – Abhijeet Dec 28 '17 at 10:56

score 0 · Answer 3 · answered Oct 29 '20 at 18:06

Almost the same as accepted one, but no scripting the initial migration.

Drop the __MigrationHistory db table
Remove all the migration files in the Migrations folder
Run Add-migration Initial in Package Manager Console
Comment out the code inside of the Up method in the Initial Migration
Run Update-database in PM Console (just to create a Migration Entry)
Remove comments in the Initial migration

score -1 · Answer 4 · answered Dec 27 '17 at 08:58

-1

Wonder how long it will be "final" ?

Use:

Add-Migration Initial

After removing the migrations folders

answered Dec 27 '17 at 08:58

ErikEJ

40,951
5
75
115

thanks but this would generate fresh create table scripts – Abhijeet Dec 28 '17 at 10:56
You didn't specify it in you question. Update question and I will let you know how to deal with it ;) – dropoutcoder Dec 28 '17 at 10:59

How to Cleanup & generate fresh migration for existing DB?

4 Answers4