Search code examples
sql-serverversion-controlredgate

Data loss warning adding column in the middle of a table

A new column has been added to a table, but the new column was not added to the end of the table definition (rightmost column), but the middle of the table.

Table differences

When I try to commit this in Redgate SQL Source Control, I get the warning "These changes may result in data loss"

  • Will data loss really occurr?
  • Is there a way preview the change script to confirm that no data will be lost?
    • Can I copy the script and easily turn it into a Migrations V2 script?
  • Will I just have to
    • Edit the table in SSMS and move the new column to the end
    • or write a migration script?
  • If so, are there any handy tools to do the repetitive stuff?
Solution

  • Up front disclosure that I work for Red Gate on SQL Source Control.

    That change will need to re-create a table. By default SSMS won't let you save that change. However that option must have been disabled in SSMS. It's under Tools->Options->Designers->Table and Database Designers->Prevent saving changes that require a table re-creating.

    Given that feature is disabled SQL Source Control has then picked that up as a potential data loss situation, and prompted to see if you want to add a migration script.

    If other developers within your team pull this change in through a get latest, then SQL Source Control will let them about any potential data loss with more details, depending on the current state of their local database. If the only change is adding columns to an existing table then this will not drop the data in columns that are unchanged.

    If you are deploying to another DB (e.g. staging/UAT/prod) and you have SQL Compare you can use that to see exactly what will be applied to a DB if you try and run this against another non-local database. Choose the create deployment script option and you can sanity check the SQL before running.

    As you say adding the column to the end of the table will avoid the need for the rebuild, so is probably the simplest way to avoid this if you don't need to worry about where the column is.

    Alternatively you can add a migration script to:

    1. Create a new table with the new structure using a temp name
    2. Copy the existing data to the temp table
    3. Drop the existing table
    4. Rename the new temp table to the original name

    You mention Migrations v2, the beta feature that changes how migrations work in order to better support branching and merging and DVCS systems. See http://www.red-gate.com/migrations

    Version 1 migration scripts will need some modifications in order to be converted to a v2 migration script. It's a fairly trivial change. We're working on documenting this at the moment, and please reach out to us on the Google Group if you'd like more information on this change. https://groups.google.com/forum/#!forum/red-gate-migrations