Options

SQL Compare scripts folder - rename a table

baranyvikibaranyviki Posts: 2 New member
edited June 22, 2020 8:36PM in SQL Compare
Hi all,
We're storing our database schema in git as scripts folder and using SQL Compare 14 to deploy its changes (automatically with teamcity) to development environments.
My problem is that when I'm renaming a table, the deploy script will drop the old table and create it with the new name and tables all data is lost.
I found that working with SQL Source Control provides migration scripts to achieve actual rename in deployment but cant find how to apply this to scripts folder.
Can this be done at all? Or any other idea how to achieve this?

Thanks in advance
Viki
Tagged:
· Share on Twitter

Best Answer

  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    edited June 23, 2020 10:40AM Answer ✓
    Hi Viki,

    Migration scripts in SQL Source Control is deprecated:
    https://documentation.red-gate.com/soc/common-tasks/working-with-migration-scripts

    They were in theory a simple solution, but in practice they were hard to manage and troubleshoot. Redgate has since delivered a couple of better options that are more reliable.

    Instead of using migration scripts, you should look at one of these two solutions.

    Pre/Post deploy scripts
    https://documentation.red-gate.com/soc/common-tasks/working-with-pre-post-deployment-scripts
    Use a pre-deploy script to check whether the rename is required (pre/post deploy scripts need to be re-runnable) and, if so, sp_rename the table with a temporary name (to archive/protect the data) and recreate the original table (so the deploy script doesnt fail). Then use a post-deploy script to check if your temp table name exists and, if so, to tidy up.

    Then upgrade SQL Compare to v13.4.7 or later and SQL Compare will use you pre/post deploy scripts when it runs the update.

    SQL Change Automation (SCA)
    Redgate will advise you to start using SCA projects for deployments. You can learn more about that here:
    https://www.red-gate.com/hub/university/courses/sql-change-automation/sql-change-automation-2

    SCA projects are primarily based on migration scripts, so you can override the default SQL Compare drop/create script with a simple sp_rename script in the source code. It's a really good solution but it would be a big change for you and you might need a new licence. (SCA requires full SQL Toolbelts for all developers.)

    There is a relatively new feature in SCA that allows you to use SQL Source Control for day to day development and then the SCA plugin for SSMS to convert your SQL Source Control projects into SCA projects when you want to release your updates.

    If neither of those solutions float your boat, I'm afraid the best option is to use SQL Compare to generate the script and then edit it and execute it manually.

    Alex
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
    · Share on Twitter

Answers

Sign In or Register to comment.