How do I AUTOMATE SQL Source Control to do a Get Latest of all dev databases nightly

icco

We are a team of developers and each one has their development database linked to SQL Source Control. Is there a way to automatically execute a Get Latest on each database from a command line or API? If there are conflicts then I would understand it would not let me. But typically there aren't. This would save me a TON of time.

AlexYates

The short answer:
No. SQL Source Control is opinionated about that being a conscious decision by a developer.

The longer answer:
Assuming your source control system has a half decent command line, you should be able to script it using the sqlcompare.exe command line (assuming you have an appropriate licence) and the CLI for your source control system.

The process does a get a bit complicated though. SQL Compare can compare two versions of the code, but to do a "Get Latest" you need to do a three way comparison. (For mor info about why, read this: https://documentation.red-gate.com/soc/reference-information/how-sql-source-control-works-behind-the-scenes.)

For this reason, I wouldn't do this lightly - it's more complicated than you think. I would also only do it if you were using a modern distributed source control system that handles branches elegantly. (Git.) If you are using git (or a similar dvcs) then you can use branches to avoid the three way comparison pain.

If I was going to do it, I would save a script that does something a bit like the following into your repo, and then I'd use some scheduling tool to run it on a nightly basis on each of your developer's machines.

> git status (Are there any commits that I need to pull down?)
> if no updates, stop here. You are up to date already. Otherwise:
> sqlcompare.exe (deploy any differences on local DB to branch)
> git commit -m "auto updated from dev env at <timestamp>"
> git pull
> if merge conflict, error out. The developer has merge conflicts to fix in the morning
> if no merge conflict: sqlcompare.exe to deploy source code to local dev DB

For reference, this doesn't just do a "Get Latest", it also does a "Git Commit". If you want to avoid that you should review the documentation above and think about how to manually do a 3-way compare.

On balance, I would be nervous about doing this. However, if you still wish to do it, I hope the information above helps.