Notes on Flattening Migrations

Here are some quick notes on a method to recreate migrations from scratch.

• Use Visual Studio 2012 to create scripts for the new first migration so that the database will have all the same data as one would have had if they had migrated thru all the old migrations

  • From the Visual Studio menu, choose SQL, then Data Compare (not Schema Compare). If you don't see Data Compare in the menu, select SQL | Check for Updates to make sure you have at least the June 2013 version of the SQL Server Data Tools (SSDT). You can also get them from http://msdn.microsoft.com/en-us/data/hh297027

• Data Compare Notes

  • You need two databases.

    • First, create a new database and do Update-Database to get it updated with all the migrations. Let's name the database "RockChMS_Clean"
    • Next, using Sql Server Management Studio, right click on your clean migrated database and choose Tasks | Generate Scripts. Do all the default settings on the wizard and you'll get a script that recreates the database, but with no data. Use this to create an empty version of the Rock Database. Edit the scripts so that the new database is named "RockChMS_Empty", and then execute it

  • Use Data Compare and make the Source Database "RockChMS_Clean" and the Target Database "RockChMS_Empty".

    • Follow all the steps in the wizard and you'll see a grid.

    • You will see a list of tables to generate scripts for. The ones that are checked are ones that have data in them. We all of those except for a few, so uncheck these -[_MigrationHistory] -[Audit] -[ExceptionLog]

    • At the top left of the grid you'll see a button "Generate Script..". Click it. You'll get a nice script that you can stitch into your new first Migration.

• Now, back in Visual Studio, you can delete all the migrations in the Rock project, and start over with a brand new first migration.

Notes about what should be in the first migration

• the result of "Add-Migration CreateDatabase" (your new first migration)
• Any special Schema things that the Add-Migration did not figure out.
• Computed Columns. HINT: Look in the script that created the empty database
• Indexes that Add-Migration didn't figure out. There are some on [Person] and possibly others. You might have to review all the migrations that have the phrase "CREATE INDEX" in them to figure out what's missing. Another idea is to try running the migration and then using Schema Compare to see what needs to be put in manually. Maybe that could be the 2nd migration :)