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