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

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

Just a question. Are you using a different schema other than dbo?

I think its a bug in EF framework where it doesn't check the schema when it does it checks to see if the __MigrationHistory table exists.

I was able to solve this problem by just creating a Dummy __MigrationHistory table with the dbo schema, this tricked the EF6 generator for "creating the table"

CREATE TABLE [dbo].[__MigrationHistory] ( [MigrationId] [nvarchar](150) NOT NULL, [ContextKey] [nvarchar](300) NOT NULL, [Model] [varbinary](max) NOT NULL, [ProductVersion] [nvarchar](32) NOT NULL, CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY ([MigrationId], [ContextKey]) )

If you want to automate it, you'll have to create a Empty Migration with a dummy class that uses the dbo schema and run that migration first to generate the relevant tables. Then run the scripts for the migration with the different schema and it should work.

Answer 2

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