none
MigrationHistory being a systems table RRS feed

  • Question

  • I am having trouble with the __MigrationHistory table being a systems table.  We have been using DB Ghost from Innovartis to create migrations and are very happy with how it has been working out for us.  However, DB Ghost does not support upgrading system tables.  I can appreciate this behavior; DB Ghost is all about migrating user data, not system data.


    For us, it would make more sense if __MigrationHistory was a normal user table, not a system table since then we can keep using DB Ghost to migrate our databases.


    Is there an option to create the __MigrationHistory as a user table?
    Thursday, February 23, 2012 6:44 PM

Answers

  • Hi Altenstedt,

    One way to make sure that __MigrationHistory is not a system table is to wrap the SQL generator. This only works if you do it before the database is created and then use Migrations to explicitly create the database with a call to update-database. Create a class that derives from SqlServerMigrationSqlGenerator and override GenerateMakeSystemTable so that it does nothing:

    public class NonSystemTableSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        protected override void GenerateMakeSystemTable(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
        {
        }
    }

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

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

    If you have an existing __MigrationHistory table and want to make it non-system then you’ll have to do some work in SQL.

    SELECT *
    INTO [TempMigrationHistory]
    FROM [__MigrationHistory]
    
    DROP TABLE [__MigrationHistory]
    
    EXEC sp_rename 'TempMigrationHistory', '__MigrationHistory'
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Altenstedt Tuesday, February 28, 2012 8:49 AM
    Tuesday, February 28, 2012 2:15 AM
    Moderator

All replies

  • Hi Altenstedt,

    Welcome!

    We will do some more pending research  about your problem and come back as
    soon as possible, Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, February 25, 2012 1:25 PM
    Moderator
  • Thanks, Alan.
    Sunday, February 26, 2012 9:32 AM
  • Hi Altenstedt,

    One way to make sure that __MigrationHistory is not a system table is to wrap the SQL generator. This only works if you do it before the database is created and then use Migrations to explicitly create the database with a call to update-database. Create a class that derives from SqlServerMigrationSqlGenerator and override GenerateMakeSystemTable so that it does nothing:

    public class NonSystemTableSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        protected override void GenerateMakeSystemTable(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
        {
        }
    }

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

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

    If you have an existing __MigrationHistory table and want to make it non-system then you’ll have to do some work in SQL.

    SELECT *
    INTO [TempMigrationHistory]
    FROM [__MigrationHistory]
    
    DROP TABLE [__MigrationHistory]
    
    EXEC sp_rename 'TempMigrationHistory', '__MigrationHistory'
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Altenstedt Tuesday, February 28, 2012 8:49 AM
    Tuesday, February 28, 2012 2:15 AM
    Moderator