Renaming table loses data

Jeff Clark

If I rename a table and check the changes into source control, when I run SQL Compare of the sourced controlled schema against another database, the change script generated simply drops the table with the old and creates one with the new name without copying the data. The documentation noted that Source Control would show the rename event as a drop and create, but it did not note that it would lose all data in the table during the event. Is there a way around this?

SQL Server 2008 and above have a DDL RENAME trigger. Can you use this trigger to smartly handle rename of tables?

Using SQL Compare 11.2.1.82, SQL Source Control 4.0.0.181, and SQL Server 2014 Enterprirse.

Any ideas how to make this work?

Sergio R

Hi Jeff,

Thanks for your post.

Regarding your query, the recommended way in SQL Source Control to avoid loss of data when renaming a table is to use a migration script.

Please read the following for more detailed information:

http://documentation.red-gate.com/displ ... on+scripts
https://documentation.red-gate.com/disp ... +data+loss


I hope this helps,

Jeff Clark

Hi Sergio,

Yes, I am looking for something like migration scripts. That would solve not only this issue but broader issues of adding new non-null columns to tables - which we do fairly frequently.

However, we are using Git for source control and a shared database in our development. Will either of these be supported in V2 when it gets out of beta or other version in the foreseeable future? We need a solution we can use in our production work right away.

Thank you in advance for your assistance.

Jeff

Sergio R

Hi Jeff,

We are currently working on a new migration model and enhanced Git support will be one of the main features. Shared databases should continue to be supported.

Thank you,

Jeff Clark

Thanks, Sergio.

I look forward to seeing what you come up with.

Thanks,
Jeff