Code First Migrations: Making __MigrationHistory not a system table

Code First Migrations uses a table called __MigrationHistory as a place to store metadata about the migrations that have been applied to the database. Code First creates this table when it creates a database or when migrations are enabled. In addition, when running against Microsoft SQL Server, Code First will also mark the table as a system table. However, several times recently the question has come up of how to make the table a normal user table instead of a system table. This is pretty easy to do and this post describes how.

Migrations doesn’t actually care whether or not __MigrationHistory is a system table. Indeed, with some providers, such as SQL Server Compact, the table is never marked as a system table. The reason it is marked as a system table on SQL Server is simply to keep it out of the way such that it doesn’t clutter up the view of your normal tables.

However, sometimes having __MigrationHistory as a system table can be a problem. For example, current versions of Web Deploy don’t deal well with system tables. The Web Deploy team are working on supporting Migrations but until this work is released you may want to make __MigrationHistory a normal user table.

For new databases

One way to make sure that __MigrationHistory is not created as a system table is to override the Migrations SQL generator for SQL Server. This only works if you do it before the table has been created, since Code First only tries to mark the table as a system table as part of creating the table. In other words, this method is only usually suitable for new databases where you haven’t yet performed any migrations.

To override the SQL generator, create a class that derives from SqlServerMigrationSqlGenerator and override the GenerateMakeSystemTable method so that it does nothing. For example:

  public class NonSystemTableSqlGenerator : SqlServerMigrationSqlGenerator
  {
      protected override void GenerateMakeSystemTable(
          CreateTableOperation createTableOperation)
      {
      }
  }

Now set an instance of this new class in your Migrations Configuration:

  public Configuration()
  {
      AutomaticMigrationsEnabled = false;
      SetSqlGenerator("System.Data.SqlClient", new NonSystemTableSqlGenerator());
  }

For existing databases

If you have an existing __MigrationHistory table and want to make it non-system, then you’ll have to do some work in SQL. The following worked for me although there are plenty of other ways to write the SQL that would have the same end result:

  SELECT *
  INTO [TempMigrationHistory]
  FROM [__MigrationHistory]

  DROP TABLE [__MigrationHistory]

  EXEC sp_rename ‘TempMigrationHistory’, ‘__MigrationHistory’

And that’s it—you don’t have to have __MigrationHistory as a system table if you don’t want.

Thanks,
Arthur

About Arthur Vickers

Developer on the Entity Framework team at Microsoft.
This entry was posted in Code First, Code First Migrations, Entity Framework and tagged , , , , . Bookmark the permalink.

16 Responses to Code First Migrations: Making __MigrationHistory not a system table

  1. Hello Arthur,

    I get the error ‘Invalid object name ‘dbo.__MigrationHistory” when I try to create database using EF 4.3. It was working perfectly with EF 4.2. Not sure why I get this error.

    Pls help!

  2. Andy says:

    Hi Arthur,

    Any chance you can update the post with how to resolve this in MySQL ? Getting this prob in MySQL and unsure how to resolve it from the script you have pasted for an existing DB?

  3. Sam Naseri says:

    Unfortunately I am using EF in my project. After migration support added to EF I thought using this feature may help me feel a little better about EF. But the way migration is implemented in EF shows that nothing has changed. Migration has lots of problems and bugs. After carefully doing all the required cumbersome procedure EF migration failed to work and I found no workaround until I strangely face the exception ‘Invalid Object name ‘dbo.__MigrationHistory”. This exception give me a clue about the cause of my problems. I did what I thought is solution and now it seems to be working. I guess my problem was that my default schema was not dbo and migrations’ sql generator does not specify the schema name for creating this table.
    I think if my speculation is true, then using the technique you described here would be helpf to provide a general solution for other people. I mean using SqlServerMigrationSqlGenerator and forcing sql generator to specify the desired schema for __MigrationHistory table.

    • @Sam I’m sorry you’re having issues using EF and Migrations. Currently __MigrationHistory must be in “dbo” for Migrations to find and use it. We have a backlog item to allow that to be changed. For the other issues and bugs that you have found would it be possible for you to report these or file them as bugs? We’re always looking for feedback whether that be bug reports or suggestions for improvements so we would love to hear from you.

      • Sam Naseri says:

        Yes, of course it is a simple and required feature to have some sort of control about where Migration History should be saved, should it be saved in database or in file system. If the option is database then which schema. But that is only a feature.
        What I am complaining about is a bug. There is some sort of inconsistency in creating and referencing the __MigrationHistory table. Unfortunately, I am a simple developer and I am not allowed to spend time to investigate the EF problems and file its bugs. So I just mention it here:
        1- schema name is not mentioned in the creation script. so the __MigrationHistory table will be created in default schema which is not always dbo.
        2- when querying the table you reference to dbo.__MigrationHistory, so this table should be there and have recent values.
        3- Again when inserting changes into this table schema name is not specified, so it will insert into xxx.__MigrationHistory instead of dbo.__MigrationHistory
        All you need to do is to make sure same schema name is used in creation of __MigrationHistory and inserting changes into it and also when you reference to it.

        I hope I have explained my meaning well this time.

      • Sam Naseri says:

        I wrote some code which at least works for me. Please have a look.
        https://gist.github.com/2213903

  4. Thanks Sam. I will file a bug with this information.

  5. I could only get that SELECT INTO/DROP & RENAME batch to work when I changed the last line to have brackets rather than apostrophes. I guess that’s what you meant. Great post, blogged references to it at http://www.ralphlavelle.net/2012/05/entity-framework-migrations-strategies.html. Thanks

    SELECT * INTO [TempMigrationHistory]
    FROM [__MigrationHistory]

    DROP TABLE [__MigrationHistory]

    EXEC sp_rename [TempMigrationHistory], [__MigrationHistory]

  6. Fernando says:

    Dude, this saved my life. I use AppHarbor and this was really useful to me. I copy the databases using the SQL Server Import/Export wizard which does not copy the system tables.

  7. Thank you this was very helpful – the Script Generator Wizard seems to ignore System Tables,

  8. Thanks. This was very helpful.

  9. Don says:

    Hope you are still following this. I’m attempting to do automatic migrations using Entity Framework Code First on an Azure database. It’s not working. Seems that Azure doesn’t support System Tables. Could this be my problem?

  10. @Don SQL Azure has limitations when it comes to system tables, so making __MigrationHistory a normal table might help. In EF6 we have made a change so that __MigrationHistory is never made a system table.

    • Don says:

      Thanks for the help Arthur. I’ve found that when I deploy the database with the history table as a system table using SSMS the history table is dropped in Azure. I’m going to try to try moving _MigrationHistory per your blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s