Entity Framework 4.0 - Generate change script

Question

I have build my application using EF 4.0 (MS SQL 2008) and the model is getting bigger.

My problem is when I add new tables / constraints etc. to the model, I'll uptil now generate a script to create my SQL database, But it drops all tables, so I loose all data. :-(

Does anyone know how to just generate a change script, so I can keep data and just update the database?

Thanks in advance.

Søren

Answer 1

I suggest getting the Database Generation Power Pack which will allow you to generate automatic changescripts. You just hit "Generate database from model" as usual with this package installed.

Answer 2

The way I do it is to 'develop' into 2 sql databases.

The 1st is the EF one where I do those breaking changes etc (Project_SchemaDB).

The 2nd is where I might do some data testing (Project_DataDB).

VS2010 comes with a pretty good database project that will allow you to script out changes (deltas) between databases, then apply them.

So...the procedure is this.

Change connection of EF to the Project_SchemaDB and make changes, autogenerate sql.

Now open up the database project in vs2010 do a schema compare between Project_SchemaDB & Project_DataDB and if you are happy apply the changes.

When you run your application make sure your connection is to Project_DataDB.

You can of course do more to take the tedium out of this but it's pretty robust and you get versioned changes of the scripts when you are working with a team and for deployments later on down the track.

Answer 3

If you have an instance of both the existing and new databases on-hand, you can try Redgate SQL Compare to build the change script. I can recommend this because I am currently testing it to build it into our change management deployment process (do not yet own the product, but evaluating)

Edit - I originally put sql source control but actually meant SQL compare. Fixed.