3

I'm using VS2012 and EF 5.0 with a model first approach. I am wondering if there is any good way to generate incremental DDL to update model changes without dropping all the tables and losing the data I have in there already.

user229044
  • 232,980
  • 40
  • 330
  • 338
Dave
  • 1,822
  • 2
  • 27
  • 36
  • Million dollar question. I have installed VS2012 and was mingling with EF, I am honestly disappointed how complicated Microsoft turned this into. I would love to give you an answer but I am also looking for a simple solution. I normally use Devart's Entity Developer component for Postgresql and it does exactly what I need, it updates database from model without dropping tables. I am wish to see this feature shipped with vs2012, but I keep my expectations low. – detay Aug 13 '12 at 01:17
  • What I have been doing to compensate, is generating the DDL script (generate database from model) and then running a data script that I generated from the database, which I sometimes have to tweak if I add or delete fields in tables that had data in them. And I store the data script in TFS so I can rerun to reload the data whenever I regen the database. I'd love to find some better solution but this works fairly decent as compared to losing all your data... – Dave Aug 14 '12 at 05:48

1 Answers1

1

I like to use a SQL server data project within Visual Studio to keep my data in sync with the database - it's like a mini SQL server schema store.

Basically what we are doing here is updating the schema of the data project using the model's DDL script, then comparing and pushing those changes out to the database. Just be sure to generate your model's DDL script first.

  1. Create a new SQL Server Database project
  2. Right click data project and import your existing schema from the database server
  3. Right click data project and import your generated DDL script from model first project.
  4. Right click data project and do a schema compare of your project vs. your database server
  5. Update database based on this schema compare (click update)

Every time you want to update your database just generate and import your models' sql script, compare, and update. It takes a couple steps but works perfectly.

nh43de
  • 813
  • 11
  • 11
  • I guess this is moot now that MSFT has decided to drop all but Code First which is ironic since that is the approach that most sucked when EF came out and forced most of us to use Model First or Database First. – Dave Mar 20 '15 at 19:23
  • I think the best answer going forward is to use something like DevArt who support all 3 approaches, since Sparx EA also refuses to accommodate Code First generation from their models, and Database First is no longer an option with MSFT which was useful to bring in model changes that EA made. – Dave Mar 20 '15 at 19:26