Upgrade/update SQL database to new schema version while keeping as much of the old data as possible

Question

Im not used to work with SQL and I hardly know any DDL-scripting at all. Im using ADO.NET Entity Framework 4 in Visual Studio 2010 with EDMX-designer and a Self-Tracking Entity Generator. It does the hard work for me and I get a DDL-script to be used to create the database when it doesnt exist. So far so good... Then I make a change in my EDMX-model, perhaps adding a property to an entity, and I get a new DDL for creating a new database. But what about the old one? This database now contains some data and I would of course like to move this to the new version. Ideally I would like some tool to generate a UPGRADE-DDL from my old CREATE-DDL to my new CREATE-DDL while also keeping as much of the data from the old database as possible. But I cant find any such feature in VS2010 and I cant find any resources about it when searching the net, which seems weird because this should be a very common problem, right? Or am I missing something here? I may do a lot of changes and additions to my schema using the EDMX-designer and I dont want to manually write the upgrading DDL-script. Im not good enough with DDL to do that... How is everybody else doing this? Im confused...

Answer 1

Well, welcome to the limitations of automatic db generation. Learn to live with them. Automatic schema generation from an O/R mapper is never going to work well.

Answer 2

"Entity Designer Database Generation Power Pack" seems to be the solution! It can generate upgrade-scripts for edmx-schemas. Perfect!