There is already an object named '__MigrationH

Question

When I tried to execute SQLQuery (generated by Update-Database -Verbose -f -Script locally in Visual Studio) on remote database, I see the following error returned by SQL Server Management Studio:

Msg 2714, Level 16, State 6, Line 1

There is already an object named '__MigrationHistory' in the database.

How to solve this?

Answer 1

__MigrationHistory is an auto-generated table used by EF to track what upgrades/patches it has applied to the database. EF is completely aware of that table and handles it on its own. You should ot create/drop/alter that table. It seems your database already has that table. If EF or your upgrade-script tries to create such table, this is strange. You need to carefully review everything and guess/learn what really has happened, because either EF went wild, or your scripts are prepared in a wrong way.

Answer 2

A setup for a migration might be missing. Typing the command add-migration MigrationName in the Package Manager Console before updating the database worked for me, as suggested in this tutorial

Answer 3

I opened SQL Explorer from visual studio and connected to the database. Dropped table using SQL directly. Deleted Migrations folder. Ran migrations again. As it is test database, there was no big issue in dropping table. If it has data, you need to think of a different solution. Nothing else worked.

1. Tools > SQL Server > New Query
2. drop table "table_name";
3. Solution Explorer > Right click on Migrations and Delete
4. Tools > Nuget package manager > Package manager console
5. In the Package Manager Console (typically opens at bottom of screen): Add-Migration InitialCreate4
6. Update-Database

Answer 4

I've seen this happen when doing "Code First from an Existing Database" where the database being pulled from already has __MigrationHistory table.

It ends up added a POCO class of the type. Remove the class, redo migrations and run again.

Answer 5

You should either change your connection string of your startup project to point to the remote database - it would appear that it is pointing to a database that already has a __MigrationHistory table, or generate a full script using

update-database -script -SourceMigration $InitialDatabase

which will script all migrations into a single file and check migration by migration to see which ones it needs to run. The first thing this script does is check for the existence of __MigrationHistory table and create it if it doesn't exist.

Answer 6

Change database name in the connection string in web.config solved to me after drop database. It's a workaround that helps in dev environments. The db was recreated with the new entities.