Lost migrations and Azure database is now out of sync

asp.net-mvc azure ef-code-first entity-framework-6


The quick question is "How do I get my Azure database back in sync with my local database?"

Details: I have been using entity framework in my project, which really not that different from the ASP.NET MVC 5 sample project, which has Identity 2 built in.

There are two database contexts, one called ApplicationDbContext (aspnet_somethingUserDB) from Identity and the other one called StoreInitialTestContext (store_db) that is from my changes.

Interestingly, I have each of these contexts in a separate SQL database locally, and they are in just a single database, called store_db on the Azure service.

Things were working swimmingly, and the two local databases, which had the same table complement together as the single database on Azure, were in sync. The website worked well locally for debugging and on Azure.

After making a last change to the schema by adding a model, I did the update-database command and the local database was updated. Unfortunately, when I published the website to Azure, after selecting the context and the checkmark by "run code-first migrations...", the Azure database didn't update.

Looking at my code, I don't see any migrations that are pending, so I'm not sure how I get the Azure database back in sync.

As a last-ditch effort, I set my Azure database from Basic to Standard, and used the compare schema command between my local store_db and the Azure store_db. I made sure that only tables in the local store_db were updated by unchecking the tables having to do with aspnet_UsersomethingDb.

That operation wasn't successful, so I attempted to undo the changes to the local which were the problem, and which didn't get applied on Azure.

That too wasn't successful, so I adjusted my website to avoid the EntityFramework "model vs database differences" crash so that it would at least limp along.

At this point, my local database is fine, but turning off the "code first migrations" checkbox, the Azure database is reporting:

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: The model backing the 'StoreInitialTestContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

Turning on the checkbox, the report is:

An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll but was not handled in user code

Additional information: There is already an object named 'ExtendedUserInformations' in the database.

This seems to indicate that code-first migrations want to add the object to get things in sync, but that the object already exists.

Is there just a quick way to get these databases in sync? There is not that much data in Azure, so if I have to, I can export some records and reload them after recreating the db.

Do you recommend using update-database and specifying the Azure connection string?

8/27/2016 2:33:36 AM

Popular Answer

What I ended up doing here was probably not the easiest way to do this, but it may help someone.

First, although what he proposed was interesting but didn't work in my case, a big shout to http://robertgreiner.com/2012/05/using-entity-framework-database-migrations-to-update-a-remote-database/ . I should have realized it, but the update-database command can be run on the Azure database as well by using the Azure connection string.

Make sure to have your database scaled to "standard' tier or above to avoid command timeouts and spotty operation. 'basic' and 'web' are not meant for anything "useful". Note that you can change the tier under the "Scaling" Azure settings for the database as often as you like.

One can get the Azure connection string by copy and pasting from Publish settings (right click on your project, select "Publish" and then select "Settings"), and the -ConnectionProviderName which is needed is System.Data.Sqlclient, so the command looks like this:

update-database -ConnectionString "Data Source=tcp:'your-sql-server'.database.windows.net,1433;Initial Catalog='your-db-name'; User ID=user@'your-sql-server';Password='sql-server-pwd'

The complexity is very much increased by the sample project having two database contexts, ApplicationDbContext and mine, StoreInitialTestContext. The fact that there is a database on the local side for each context, but only a single database on Azure, makes it difficult to use the DropCreateDatabaseAlways or DropCreateDatabaseIfModelChanges initializers.

I ended up scripting the contents of the largest database tables so that I could restore them later.

Next, I deleted the entire Migrations directory, which effectively disables migrations (As one had noted here, there is no disable-migrations Package Manager command).

Next, I set up a database initializer for the StoreInitialTestContext, like this:

public class StoreInitialTestContextInitializerCreate: DropCreateDatabaseAlways < StoreInitialTestContext > 
      protected override void Seed(StoreInitialTestContext context)
        // nothing for now

Also, register this as the database initializer for the proper context:

public class MvcApplication : System.Web.HttpApplication
    protected void Application_Start()

        // Drop the StoreInitialContext database and recreate if the model changes
        Database.SetInitializer<StoreInitialTestContext>( new StoreInitialTestContextInitializerCreate());

This I used to recreate the database containing the StoreInitialTestContext upon the next run of the website.

Once the local database was completely cleaned up, I moved to Azure.

I went to the portal and deleted the database, so that it could be recreated next time my website was run on Azure.

I made sure that the settings in the Publish profile were set to update the database, but noted that the code-first migrations option was not available.

The next time the website was run on Azure, the entity framework saw no database, so it was Created.

I made sure to exercise both my own context by listing items, and the aspnet Identity portions by registering as a user. Checking the SQL Server Object explorer in Visual Studio confirmed that all tables had been created.

Next, it was time to restore the data from the tables, but first to avoid Azure errors, I set my newly created database, which had been created as a "defunct" "Web" database tier, to the "Standard" tier.

I then loaded the scripts into Visual Studio one by one, entered the connection string user and password into the dialog, and ran the scripts.

There were only a few tables to update, and that went smoothly, and I was then ready for my next challenge.

1/10/2015 3:08:52 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow