0

In my Application I have used Entity Framework Database First approach. Currently my application is in Dev Environment, now it need to be moved into Test Environment and later into Production Environment.

So is there anyway that I can use .net feature or Entity framework feature to migrate/create database in Test environment. Other than using SQL feature of restoring the database. Also note that if any enhancement comes then Database structure can change, table schema can change.

So can you suggest me the best way to easily migrate database schema in different environment without losing existing Data.

Improve this question
5
  • 1
    Is your production database already using EF Migration? Commented May 20, 2014 at 4:52
  • No. It was the latest modification that we changed from Ado.net to EF Database First Approch Commented May 20, 2014 at 7:46
  • Did you moved from ADO to EF without changes to the database or Is your latest code requires database changes? Commented May 20, 2014 at 9:51
  • I have to add and modify some of the tables Commented May 20, 2014 at 12:51
  • The latest database changes is only in Dev environment, need to migrate it to Test and Production. Can I do using Entity framework or any third party tool. Also data of existing tables should not be lost. As per my understanding if I would have use code first approach then only I can use EF migration. Let me know if I am wrong. Commented May 21, 2014 at 1:42

3 Answers 3

Reset to default
1

If you want to take advantage of EF-Migrations feature, you must convert your application to Code First with Existing Database http://msdn.microsoft.com/en-us/data/jj200620.aspx

If you are unable to convert to code first then you must create the update script by hand.

  1. Use a schema compare tool, compare the development and production server.
  2. For each difference found, create an update query.
  3. Once the entire script is finished, test it on the staging server.

Automating the migration is very risky, it depends on the type and size changes you made to the schema. You can't trust any single feature or tool specially if the changes requires data motion (moving data around).

The following links might help you:

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

My Application code is using Version control but I am not using Entity Framework code first approach. I am using Database first approach where all the Entity will be automatically generated. Are you saying to convert my application from Database first to Code first model?
@Nijith - Database first does not support migrations stackoverflow.com/a/9255453/1027250. You must create the upgrade sql by hand.
1

With Database First, the easiest way to copy a schema is to extract a data tier application in management studio, create an empty database on the target, register it as a data tier application with the same name, and upgrade the empty database using the upgraded file. You can repeat this step to manage schema changes.

Having said that, going forward you're really better off switching your Database First to Code First as it will make change management across your deployments much easier.

Improve this answer

Comments

1

Migrations are best way to deal with it
Preferred way to update production db is to first generate sql file and then run the sql file in production environment.

MS had a very good artical on this

http://msdn.microsoft.com/en-in/data/jj591621.aspx#script

Improve this answer

1 Comment

But this artical is using Code First Approach. But me using Database First.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.