Introduction
As many of you know, I’ve been quite the fan of Entity Framework CodeFirst for quite some time. It does a great job of reducing the number of places I have to change thngs each time I have a database schema change and also brings a ton of type safety along with it. I’ve posted before about the benefits of the auto migrations features built into CodeFirst, however I have found when I make a lot of changes to my schema, I quickly get an error saying I will have data loss so the CodeFirst Migration will not take place.
In my realities, it is just too much time to go through with LINQ and try and make the perfect data migration. My reality is that I likely only have one or two databases that need sql updates and I can do those much more efficiently with Redgates Sql Compare. In this post, I’ll walk through the steps I follow. This is by no means a perfect solution but it is quick, solves my problem in both a reliable and robust way.
Steps
Step 1
Backup your production database! (goes without saying but saying it anyhow)
Step 2
Run Sql Compare against your most up to date database that you want to ultimately be production (and choose compare). Below is a screen shot of what this looks like for me.
The resulting comparison looks like this graphically. I’m going to choose to do all the suggestions.
Step 3
Now, I press the “Deploy Wizard” and I get a screen that tells me what it will do and gives me the script to do it. Notice that is telling me what data I will loose if any. In my case, the data that is being lost is OK because I can simply have my background email process refetch all the email and everything will be fine. You need to be very careful of this to make sure that it is what you want.
Step 4
Now, if I say “Open Script Editor” it takes me to Sql Server Enterprise Manager, connects me to the correct database and gets my script ready to go. All I have to do is press “execute” and it will run!
Side Note: You may have to remove your migration custom sql tables for this to work. I did that and also used the implementation of create only when database does not exist for my code first now, not the migrations.
Conclusion
And it’s hard not to love the results.
And of course, you have to just Love Redgate’s Sql tools. Sql Compare is just one of many. I highly recommend them for any sqlserver professional or developer.
That’s it! My production database is now the same as my CodeFirst local database.