Downgrade (Rollback) Database with code-first in p

Question

I have a web application that I install on my customers' computers for their inner use. I use C# MVC5 and code-first Entity Framework. I used automatic migration=true but I stopped and set it to false. I installed it on a production environment (release) - with a deploy-package (no Visual Studio).

I have a customer with the application - version 1. Now I want to upgrade to version 2. I want to enable to upgrade the App's DB (in production file, with CMD installation from a package), but to have the possibility to downgrade the DB if there'll be any problems - but without deleting the existing rows.

For example - if I have a table "Items" and Items has Key, Name, Location. On upgrade I add a new column: Email. On downgrade - the new column will be deleted. I created the migration on Visual Studio I get this code (It's just for the example - I have more migrations):

public partial class AddEmail : DbMigration
    {
        public override void Up()
        {
            AddColumn("dbo.Items", "Email", c => c.String());
        }

        public override void Down()
        {
            DropColumn("dbo.Items", "Email");
        }
    }

Now, I installed the App's new version on the existing version and it worked fine - the new column was added and it worked with the new code. I added a few items (rows).

Now, how can I re-install the old version so the new column will be deleted? Actually I want to rollback the new migrations - but I don't want to lose the new rows, only the new columns.

Answer 1

You can downgrade to a specific migration using this technique: https://msdn.microsoft.com/en-us/data/jj591621.aspx#specific

Keep in mind that requires a target migration so you should configure a baseline snapshot of your database using Add-Migration Initial –IgnoreChanges (see https://msdn.microsoft.com/en-us/data/dn481501.aspx) Otherwise use Update-Database -TargetMigration:0

When you rollback it will abort if data exists in the columns, so you may need to do Update-Database -Force

Answer 2

In this article there is an explanation how to create a script that migrate from one migration to another - upgrade or downgrade. So I ran in the Package Manager Console -

Update-Database -Script -TargetMigration: MyMigration1

and got the SQL script.

I needed to go through it a fix it a little and then I just ran it on the Production Database to downgrade the DB.