Allow For Arbitrary Scripts To Be Checked In to add custom logic for transitions
It's quite common, when refactoring a set of tables to be able to migrate data from an existing structure into a new structure. This typically happens in 3 steps.
Create the new structure.
Migrate the data from the old structure.
Delete the old structure.
From what I can tell of SSC, this is not possible.
SQL Source Control 3.0 is now available!
It includes a new feature called ‘Migrations’ that will help avoid data loss when renaming a table as it allows you to create, store and share SQL change scripts for accurate deployment.
For more information about Migrations, please visit:
An Early Access build of SQL Source Control 3.0 is available which inlcudes a new feature called "SQL Migrations".
This feature allows you to create, store and share SQL migration scripts for accurate, automated deployment using SQL Source Control and SQL Compare.
The installer zip can be found here:
As I said, this is an Early Acces build - so does not include all the functionality we are hoping to provide with "SQL Migrations". We'd love to hear feedback from you regarding whether this feature provides a good solution to the problem you've described.
This is on the roadmap for the next release of SQL Compare and SQL Source Control. After you have generated a script with SQL Compare, you will be able to customize it, save it, and commit it in SSMS alongside your schema changes. When generating scripts in the future, SQL Compare will know to re-use these scripts, as will the 'get latest' feature in SQL Source Control. Comments and questions welcome!
Lucent Fox commented
Adding the ability to check in a migration script would solve 99% of the strange scenarios for me, including this one. Right now I'm creating the migration scripts and keeping them in our project management software, but I have to coordinate with a large number of people to make sure that those scripts are applied at just the right time. If the tools would take care of the timing and ordering, that would help a ton.
This is an excellent point and it's clear that SQL Compare (and SQL Source Control) will handle the simple cases, but where complex refactorings and data transformations are required, these tools fall short. We're considering allowing the user to associate a migration script with a source controlled change. This script will be used upon Get Latest, as well as when creating a synchronization script when using SQL Compare. If we were to add this, would this solve your issue?
AdminChris Auckland (Admin, Redgate) commented
Have you tried the 'split table' functionality of SQL Refactor? - This will also be added to SQL Prompt 5 when it's released later this month.
Everything in the Object explorer that is in the DB is fine.
Everything in the Solution Explorer is totally ignored – SVN wise.
It is as if the red gate code has no knowledge of an SQL Project (a file system activity that happens to connect to a database that it does know about).
It is as if your DB (that is green) is the only thing that matters, any Scripts ( eg as part of a MS Build, MS Test, MS Deploy or Process pipeline are ignored) so there is the exposure/vulnerability of the other half of your project.
Lucent Fox commented
Slight correction ...to need to be able to migrate data...