Process for Comparing and Deploying Database Differences
Shawn
Posts: 3
Hi,
I like to know what the process is for updating an older version of my database (I will call Database A) with the differences between my latest database(I will call Database
.
When I ran the compare tool I have a table where the column is changed from IS NULL to NOT NULL. So I assume I need a migration script? The only place I seen to create this script was in the SQL Source Control where I created a repo on my local machine to Database A. The migration script simply runs an update that sets the columns values to a default value. Now the tool says "Deployment will happen in the order scripts are committed.". So the migration script is the first thing I commit to the repo.
Note: There is still a list of uncommitted schema changes.
Here is what the migration script does
UPDATE dbo.XMS_Fields
SET IncludeInReport = 0
WHERE IncludeInReport = NULL
I go back to SQL Compare and hit the DEPLOY button after its finished comparing between Database A and Database B then select what to actually deploy. This brings me to a window where it lets me know my table XMS_Fields is being changed with a column that has no default value. (That's fine, that's what I made and committed the migration script for)
When I click 'Generate Deployment Script' and skim through it, I see nothing that handles the column being altered to NOT NULL, I expected to see a snippet or something from my migration script into the deployment script.
I feeling like my process for doing this is a bit off. I am doing this for my company and if this tool really works out for what we want to do I can see purchasing the tool for further development.
Can anyone help me on the correct process for updating an existing database with differences from another database?
I like to know what the process is for updating an older version of my database (I will call Database A) with the differences between my latest database(I will call Database
.When I ran the compare tool I have a table where the column is changed from IS NULL to NOT NULL. So I assume I need a migration script? The only place I seen to create this script was in the SQL Source Control where I created a repo on my local machine to Database A. The migration script simply runs an update that sets the columns values to a default value. Now the tool says "Deployment will happen in the order scripts are committed.". So the migration script is the first thing I commit to the repo.
Note: There is still a list of uncommitted schema changes.
Here is what the migration script does
UPDATE dbo.XMS_Fields
SET IncludeInReport = 0
WHERE IncludeInReport = NULL
I go back to SQL Compare and hit the DEPLOY button after its finished comparing between Database A and Database B then select what to actually deploy. This brings me to a window where it lets me know my table XMS_Fields is being changed with a column that has no default value. (That's fine, that's what I made and committed the migration script for)
When I click 'Generate Deployment Script' and skim through it, I see nothing that handles the column being altered to NOT NULL, I expected to see a snippet or something from my migration script into the deployment script.
I feeling like my process for doing this is a bit off. I am doing this for my company and if this tool really works out for what we want to do I can see purchasing the tool for further development.
Can anyone help me on the correct process for updating an existing database with differences from another database?
Comments
Thanks for contacting us and sorry you are having this issue- this is just a copy-paste of what I sent via email.
I assume that you used this documentation when working through the migration script https://documentation.red-gate.com/disp ... t+examples
Can you confirm that you first committed your Update Migration script (the one you sent over) then committed the NOT NULL constraint change to the column - then went to deploy using SQL Compare?
If the NOT NULL column change was already made/committed prior to committing the migration script then you will need to follow the steps for Historical Migration scripts https://documentation.red-gate.com/disp ... on+scripts
Do let me know if you have any issues with that!
Technical Sales Engineer
Redgate Software
I can confirm I committed the migration script before doing any deployment using SQL Compare. However after this step I am confused as to what should happen.
You mentioned I should commit the NOT NULL constraint change to the column after the migration script. Do I need to make another script to do this? I ask this because I assumed when I was comparing my out of date DB, the deployment script that gets generated was going to do this, which when glancing over the script, it does.
For sanity's sake I started from the beginning, created a copy of my DB to be migrated.
- I linked the DB to a source control (in this case I just linked it to a local folder).
- I immediately commit the schema changes to the source control this time
- I create a blank script for the actually migration for the table that needs the NOT NULL and commit it
At this point I am confused, do I need another migration script to make the NOT NULL change?
I do the SQL Compare and select the table that needs the NOT NULL. As soon as I do, the compare SQL Compare gives me a warning that migration scripts won't be run at the top of the window. Sure enough, the deployment fails.