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?

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

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[BenjaminPaul]

What is the script you are attempting to run?

[Sachin Kainth]

This would only happen if the script contains a 'create table __MigrationHistory' command in it. Does it? Though I can't imagine why update-database would do that.

[Amirhossein Mehrvarzi]

@BenjaminPaul Do you wanna see it's contents? If yes, I'll place that :)

[quetzalcoatl]

@MahmoudHboubati: thank you for fixing that typo! I know that it's hard to fix a single-letter error as the site requires the change to be larger, but in such cases you can always add a comment like "hey, you've got a typo in foobar" and the autor will correct it. You really shouldn't damage the text by randomly replacing dots with commas or removing whitespaces.

[Amirhossein Mehrvarzi]

True :), But this occurred when I had a migration setup created with the same command! It looked something related to EF automatic migration had not worked properly and caused to generate a SQL command to re create the __MigrationHistory table again, instead of update!

[Emanuele Bellini]

OK. So I hope my answer could be useful to others, as I didn't see jbutler483's in the comments to your question.

[TravisO]

While this answer is informative, it isn't helpful and doesn't allow anybody to trouble the problem.

[quetzalcoatl]

@TravisO: that's partially true, but this is as far as we can get without more information. Aside from some really exotic things like heavily damaged database server installation, that's the only typical possible case: "that script is wrong", so he has to double or triple-check the script to see why it did try to create this table (while scripts from EF usually wouldn't). This is obvious, but the OP has this script, and haven't said he tried looking at it, and didn't ask about why some pieces of that script tried that create table, so.. what else could I/we advise him?

[Gert Arnold]

This statement can't possibly have thrown OP's exception. First thing it does is check if the database exists and does nothing if it's there. OP's database clearly existed. And when the database doesn't exist, EF is surely not going to try and add __MigrationHistory twice.