For the ones that have many databases to keep under source control, it can be really useful to speed up the link-to-source-control process. The only way we have now is to use the GUI provided by Red Gate SQL Source Control. Actually, there’s a github project called SOCAutoLinkDatabases by Matthew Flat, a Red Gate engineer, but, unfortunately it works only on a shared database model (centralised) in TFS. Let’s see how to manage the link using Working Folder (which is also good for many SCM) and dedicated database model (distributed).
The problem
We have ten databases for each developer and we are working on a dedicated database model sandbox. We have frequent changes, merges and we are dealing with a continuous integration environment. Thus, we need to redo the databases often. This means that we need to drop/unlink them often. Why? because we generate many custom/fake data and we often must delete them. In most of cases, it is quicker to drop the database instead of writing code. Say, we are developer in a development environment, we really want to get the latest version with no obstacles.
I’m used to follow these steps for each database manually:
- Create the new database with a script
- Link the database to the source control
- Choose the model
- Choose the type of SCM (Working Folder)
- Map the folder
- Apply the comparison option
- Apply the filter
- Apply the latest version
The first two options (I’m a pretty quick one) took 1 minute (with no errors). Being optimistic, 10 databases, 10 minutes. Since we are on a release branch strategy, that value should be multiplied by the number of the branches, but it’s not mandatory to get them all, actually.
Let’s speak about 10 minutes, 7 developers. More than 1 hour.
I have thought that I could save time with automation. Unfortunately, the SQL Source Control API does not provide commands for getting from the source control and applying changes to the SSMS hosted database. Thus, I could automate just point 1 and 2.
Actually, with comparison SDK (which requires a license) it is possible to diff the SCM workspace folder with the database in order to bring it to SSMS, but it is not free, and it cannot be shared on the team, due to the license per developer.
The UnlinkDropRelinkDatabases.ps1 PowerShell script
With UnlinkDropRelinkDatabases.ps1 PowerShell script, we can link automatically databases to source control also in case of Working Folder management (which includes the most famous distributed SCM). It’s an extension of the script of Matthew (my thanks to him). In the attached package you can find:
Alessandro Alpi's Blog 
I am trying to utilize this blog to make it work for our project. I am new to GIT and powershell. Looks like git init will initialize a master branch, but what if we need to initialize a feature branch? Also by doing git init, I am only seeing files config, description, and HEAD are created. But if I use Redgate to link the db, some extra files, such as COMMIT_EDITMSG, index and etc. are created. Can you give me some suggestion about the command that I should use to initialize git to generate the result as doing it via Redgate? Your help is greatly appreciated.
You are speaking about the git engine, which is not really described in this post. This tool is working on a “Working folder” scenario provided by Redgate. If you would like to make it work on git, you’ve to change the SourceControl settings to “use your source control manager” and choose “git”. So you’ve to refactor the part of “linkeddatabases.xml”, too. I really suggest you to contribute to the repo for supporting git! I’ll appreciate it 🙂
About feature branch and Redgate: https://www.red-gate.com/simple-talk/sql/database-administration/database-branching-and-merging-strategies/