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.

Nijith
  • 25
  • 8
  • 1
    Is your production database already using EF Migration? – Yorro May 20 '14 at 04:52
  • No. It was the latest modification that we changed from Ado.net to EF Database First Approch – Nijith May 20 '14 at 07:46
  • Did you moved from ADO to EF without changes to the database or Is your latest code requires database changes? – Yorro May 20 '14 at 09:51
  • I have to add and modify some of the tables – Nijith May 20 '14 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. – Nijith May 21 '14 at 01:42

3 Answers3

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.

Jeff Dunlop
  • 891
  • 1
  • 7
  • 20
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

Anshul Nigam
  • 1,438
  • 1
  • 11
  • 24
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:

Community
  • 1
  • 1
Yorro
  • 9,772
  • 3
  • 31
  • 42
  • 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 May 21 '14 at 06:00
  • 1
    @Nijith - Database first does not support migrations http://stackoverflow.com/a/9255453/1027250. You must create the upgrade sql by hand. – Yorro May 21 '14 at 07:33