SQL Compare scripts folder - rename a table

baranyviki

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

AlexYates

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

baranyviki

Hi Alex,

Thanks so much for the detailed answer, its really helpful. 
We decided to go with Pre/Post deploy scripts for now, and it works good  
Also its good to know that there are other solution for this.

Viki

AlexYates

Fantastic to hear you got it working!

By the way, whether or not you decide to adopt SCA projects, I highly encourage you to look at the SCA PowerShell cmdlets for executing your deployments with TeamCity, rather than SQLCompare.exe:
https://documentation.red-gate.com/sca/reference/powershell-cmdlets

There is even a TeamCity plug-in for them so you probably don't need to write any code yourself:
https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/teamcity

Once you get it up and running, it's much easier to maintain your deployment pipelines and you'll start benefitting from all the new PowerShell stuff Redgate has been working on.

Happy deployments!