SQL DB migration guidelines --- Updating database models ... can I copy old records

Question

Let's say I wanted to update my ASP.NET MVC website. All data for different views is stored on two SQL points, one for roles and membership, and the other for content. If the table in db is based on a specific model and I modify this model, how can I combine data from the old model into the new? Preferably automatically.

I am currently using DropAndCreateIfModelChanges. I understand that I will most likely need to copy db and say that production db and dev db will handle all model changes. Dev db, in turn, must be copied into production.

Or in SQL mgmt I can manually add new columns in SQL mgmt and not discard the database with my data? This will work too.

Thanks in advance ... may not return to today's reading ...

EDIT: Therefore, the information presented below may recommend a tool for db migration Using ASP.NET 4, IIS 7.5, and SQL 2008 R2. What is the easiest to integrate, which ones are simple and which are more advanced?

Answer 1

I use and recommend DbUp because it is simple and script, but there are several other migration tools, for example:

• SubSonic Migrations
• Rikmigrations
• Migrator.net
• Machine migrations