Code First Migrations: Making __MigrationHistory not a system table

https://blog.oneunicorn.com/2012/02/27/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 fromSqlServerMigrationSqlGenerator 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