CODEWITHSUNDEEP
entity-frameworkef-code-firstdatabase-migrationentity-framework-migrations
Mickael Caruso
Mickael Caruso

Reputation: 9491

How to Manage EF Migrations Between Development and Production Databases?

Before anyone marks this as a duplicate, none of the questions similar to this addressed any of my concerns or answered any of my questions.

I am currently developing all the POCOs and data contexts in a library project, and running migrations from within this project. The database I'm updating is the development database.

What do I do if I want to create the current schema to a fresh, new database? I figure that all I have to do is to change the connection string in web.config and run Update-Database, correct?

While the live/production database is up and running, I want to add new columns and new tables to the schema, and test it out in development. So I switch back the connection string to the development database's connection string, and run Update-Database.

Going back and forth between two databases seems like I'll get conflicts between _MigrationHistory tables and the auto-generated migration scripts.

Is it safe to manually delete the _MigrationHistory tables in both databases, and/or delete the migration files in /Migrations (so I'll run Add-Migration again)? How do we manage this?

Upvotes: 2

Views: 3914

Answers (4)

alikuli
alikuli

Reputation: 546

My production and my developmental database went out of synch and it gave me endless problems. I solved it using a tool from Red-Gate to match up the databases. After using the tool, the databases were exactly the same but my migration was not working and I started to get odd errors i.e. trying to add tables/ columns that already existed etc. I solved that. I just deleted the migration folder on the local, recreated it, added the initial migration, updated the database and then matched the data of this migration file (local) to the one on the host (delete all the data in the migration file on the host, and add the same data that is on the local into the host). A more detailed explanation is at:

migration synch developmental and production databases

Upvotes: 0

d.popov
d.popov

Reputation: 4255

Me also had the same problem, even when using one and the same database - due to some merges in the repository, and the mix of automatic/manual migrations. For some reason the EF was not taking into account the target database, and calculating what scripts need to me executed, based on what is already in the database.

To fix this, I go to the [__MigrationHistory] table on the target database and get the latest migration name. This will help EF to determinate the state of the DB, and will execute just the scripts needed.

then the following script is run:

update-database -script -sourcemigration {latest migration name}

This creates update script that is specific to the target database (the connection string should be correct, as discussed in the other comments) you can also use -force parameter if needed

this way you can update any database to latest version, no mater in what version you found it, if it has MigrationHistory table.

Hope this helps

Upvotes: 0

Kevin Shanahan
Kevin Shanahan

Reputation: 109

What do I do if I want to create the current schema to a fresh, new database?
- Yes, to create fresh database to the current migration level you simply modify the connection string to point to a database that does not yet exist and run update-database. It will run all the migrations in order.

As far as migrating to the Production database, I am running the update-database command with the -script switch to acquire the raw sql and then applying that script to the production database manually. This is helpful if you need to keep a record of sql commands run against the database as well. Additionally, you can generate the script explicitly from a specific migration to another specific migration via some of the other update-database switches.

Alternatively, you can create an Idempotent script that works from any migration by using the
–SourceMigration $InitialDatabase switch and optionally specify an end migration with –TargetMigration

If you delete the _MigrationHistory tables you will have issues where the generated script will be trying to add columns that already exist and such.

You may find the following link helpful:
Microsoft Entity Framework Migrations

Upvotes: 4

eoghank
eoghank

Reputation: 1043

I would suggest having a separate trunk in your source code repository - one pointing to production and one to development to avoid risks of switching between the two in visual studio.

Upvotes: 0

Related Questions