It's a third party tool but it's free so why waste time re-inventing the wheel? However, if I was you I'd look at Redgate DLM Dashboard. There's another great Phil Factor blog post on this topic that details how to create your own automated process for tracking drift. Keeping track of the changes that occur directly on production, for example, when people make hot fixes without going through source control. Separately it appears you have an audit concern. Automating with SSDT (Microsoft) You are probably best off starting by looking at one of the following (or looking up any of the other names I mentioned above): It's clear from your question that you want to improve your processes. However, adopting database source control and automating the release process is fantastically valuable so I do encourage you to keep going. There is also quite a lot involved here and way more than can be discussed in a single SO response. Given that there are so many options it's not possible to provide a straight forward step by step solution without knowing which database source control tool and which automation tool(s) for builds/release management you use or without recommending one. Of course, exactly how this all works (and how well it all works) will depend on the tools you use. This can either be done each commit or at the click of a button.
For the automation most people use an automation tool (or a pipeline of tools) like TeamCity, TFS/VSTS, Jenkins and/or Octopus Deploy to package up the source code and (optionally) deploy it to a database (or several databases). The packaging and deployment of this source code absolutely can be automated. Some of the most popular are SSDT, Redgate SQL Source Control, Redgate ReadyRoll, Flyway, DBup, Liquibase and DB Maestro but there are many others. There are a range of Microsoft, third party and open source tools that help you to script out your database and get it into Git (or any other source control system). It also ensures that the production database is in line with the version that you tested. This gives you the opportunity to test your code in dev before deploying it to production. In general you should be making changes in source control first and then deploying to your production databases from source control. It's a pretty big topic, I'll do my best to keep this as short as possible. Welcome to the world of Database Lifecycle Management (DLM). I'm not sure it's sensible to automate this part of the process, as you will probably want to consider why these changes have occurred.
On detecting such changes, you then have a couple of options - roll back the change, fire the DBA, update the files in source control, etc, etc. This can be done with database triggers, or, I believe, by some commercial products (that generally use database triggers behind the scenes). Given that the history of your "deliberate" changes will be in source control, the main concern of audit is detecting uncontrolled change in production. Regarding audit, you have a couple of options. Presumably it is only a subset of the data in the database - the "static" or "reference" data - that you need to store in source control? The most common way to do this is using post-deployment scripts in the database project. To do this (with SSDT), you only need to update the database project manually once, and add the resulting files to source control.Īfter that, you can make changes in the project first, commit them to source control, and then deploy these changes to your database. IMV, You should probably be looking to solve the "source control" and "audit" problems separately. Make changes in production -> Update Source Control I am however familiar enough with said tools to know that the intended use case is not I believe the Redgate tools - with which I am not particularly familiar - have some support for updating source control from a deployed database. NET project, would you be patching the binaries on the server using a hex editor and then decompiling the results into a csproj and associated cs files to store in source control? This sounds ridiculous, but it is analogous to the workflow you are suggesting for your database projects. Is not well supported by SSDT in particular the part about updating a project based on changes to a database. Make changes in database -> Update Database Project -> Commit changes You may need to rethink your approach a little.