I am working on a system that currently has a number of environments (test, stage, live, etc) each with their own database. So far these databases have been kept in sync by running the same update scripts on each of them.
We are now moving to using EF6 code first migrations, and would also like to start writing some automated system tests using LocalDB.
I've found https://msdn.microsoft.com/pt-pt/data/dn579398 which describes two options for adding an initial migration.
The first method creates an empty initial migration which will work great for the existing environments but won't help with creating LocalDBs for testing.
The second method creates a migration to bring up the whole database from scratch (minus things EF doesn't care about such as sprocs and views). This would be acceptable for testing, but not good for actually recreating a databse. It also requires you to manually comment out the Up method, run the migration on all existing databases, and then put the Up method back. As it will take a while to get the migration through all the environments I'm not keen on this. It also violates the one of the principles of migrations which is that they shouldn't be edited once they've been released.
Having some kind of conditionality in migrations would solve my problem (e.g. if(tableExists("A_table_in_the_existing_database") return;) but there doesn't seem to be anything like that available.
The best I've come up with is to dump the existing database schema from SQL server to a file (which has the advantage of preserving sprocs, views, etc) and then use option 2 above, except instead of using the generated Up method I'll run the SQL file.
However, this still has the drawbacks of option 2 mentioned above, so I'd be very happy to learn of a better way of handling this situation.
Would this work? Run the commented out initial migration on one database, then dumping out the __MigrationHistory table and inserting it into the other databases? That way I wouldn't have to wait for the migration to make it through all the environments before I could safely uncomment it.
EF 6.1.2 has support for running SQL embedded as a resource within the assembly containing the migrations using the SqlResource method.
I think I'd go with scripting out your existing schema and using an initial migration that executes
SqlResource as its
Up. If all it's doing is a bunch of
IF EXISTS then it shouldn't take too long to run. Otherwise scripting out
__MigrationHistory will also work if you just want to run it once locally and apply to all your other databases by hand.