IT:AD:EF/CodeFirst:HowTo:Reset and Consolidate Migrations
Summary
After a while, a project ends up with a ton of Migration scripts.
As each one is big, they get a bit unwieldy when generating Schema+(Selected) Data Scripts to give to UAT and PROD.
Process
- Get stable first (ie, get to a point where you have checked in everything into your gated checkin protected CI Build Server, and synced, and all that jazz).
- Delete all the Migrations (yup..don't worry, you're change controlled…)
- Create a new Migration, (eg: named
Initial
):
add-migration Initial -projectname "4.App.Core.Back.Infrastructure" -startupprojectname "1.App.Core.Back.AppHost.Web" -connectionstring "data source=localhost;initial catalog=NSI_SS_06;integrated security=True;MultipleActiveResultSets=True;" -connectionProviderName "System.Data.SqlClient"
- Run your unit tests – one of which should be creating the db from scratch, running all migrations (at this point back to being just one).
- Using SSMS Open your local db, export a script of the MigrationsTable's data:
USE [NSI_UNIT_TEST] GO INSERT [dbo].[__MigrationHistory] ([MigrationId], [ContextKey], [Model], [ProductVersion]) VALUES (N'201503222229574_Initial', N'System.Data.Entity.Migrations.DbMigrationsConfiguration`1[App.Core.Infrastructure.Data.DbContexts.AppDbContext]', 0x1F8B080000000000040....foo...bar...0395C00600, N'6.1.3-40302') GO
- Bring that script over to the build server – which still has all the Migrations.
- BACKUP THE CI DB!
- Clone the Migrations table as a safegaurd:
* Delete all the migrations in the CI Db (gulp)
- Run the script containing just one migration (the one you copied over before)
- The CI server now has one migration.
- Check the code…pray…
- If it worked, delete the cloned tabled as it's no longer needed, and might as well cleanup.
- Use IT:AD:SSMS to Export Script wizard of the schema. Specifically:
- Select the database
- RIght click, select Tasks/Export Script
- Select Tables only - no stored procs, functions
- Using the Advanced option on the next wizard page:
- Script Indexes=true
- Type=SCHEMA only
- Leave:
- Script Triggers=false
- Users/roles=false
- Export to screen
- That should be it.
TroubleShooting
I ran this for hours, and kept on getting an error summarized as follows:
Setting DbMigrationConfiguration's Connection String to: * Server=CORP;Database=APP_CI;User Id=SomeOne;password=Something;Connection Timeout=1200; Setting DbMigrationConfiguration's Provider to: * System.Data.SqlClient There are 0 db migrations: * 201503232101267_InitialCreate There are 1 local migrations: * 201503232101267_InitialCreate There are 1 pending migrations: * 201503232101267_InitialCreate
Which was obviously not right (I wanted it to be 1 db migrations, 1 local = 0 pending).
The error was that I took the Migration entry from a Unit Test – where the Context Key was System.Data.Entity.Migrations.DbMigrationsConfiguration1[App.Core.Infrastructure.Data.DbContexts.AppDbContext]
and not what was in the app – which was App.COre.Infrastructure.Data.Migrations.Configuration
.
Now that EF6 is Context aware, this key is important. It has to match.
So I went back to the __MigrationHistoryBackup table and copy pasted the server value into the new record.
Now the result was what I wanted:
Setting DbMigrationConfiguration's Connection String to: * Server=CORP;Database=APP_CI;User Id=SomeOne;password=Something;Connection Timeout=1200; Setting DbMigrationConfiguration's Provider to: * System.Data.SqlClient There are 1 db migrations: * 201503232101267_InitialCreate There are 1 local migrations: * 201503232101267_InitialCreate There are 0 pending migrations: * 201503232101267_InitialCreate
Next problem…