SQL Compare - Automate multiple DBs

owencampbell

Hi, I've got an instance with say 50 DBs on. I have a Seed DB that is cloned whenever a new client comes on board and at the moment I currently compare what I've got in source control to the seed schema, generate a script and then use Multi Script to pass it out to the other 49 instances

We have had an issue with people making changes to DBs outside of source control for individual DBs as and when issues cropped up. 

I can sit and compare all 49 DBs to the seed 1 at a time via SQL Compare but that is time-consuming and it won't scale as the app grows. Is there a way to automate the compare process with the Red Gate tools?

Sergio R

I assume that you have a SQL Source Control\SQL Compare scripts folder on Source Control, is that right?

The best option here would be to use SQL Change Automation, more specifically the Powershell module.

This should enable you to create a script that would create a build from source control and then deploy that build to all the databases.

We have had an issue with people making changes to DBs outside of source control for individual DBs as and when issues cropped up. 

Do you want to automatically correct this schema drift?

1- If you do you will need to create a release for each database in your script (so that it deploys the right script)

2- If not you will create a single release based on what is the expected schema of the target and then deploy this release to all the databases.
Since SQL Change Automation Powershell does a Pre-Update Schema Check any deployments to databases that have an unexpected schema will fail

Please see the following document for more information regarding this:
https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-source-control-projects