Andrew's ramblings

Version control your SQL Server with Red-Gate SQL Tools

Tags: Version Control, General, SQL Compare, Database

Today I am going to explain how to use Red-Gate's Compare tool to compare a database with a folder, in which you can then use to store in your version control system.

The one thing that I have been looking for was a way to version control a database schema, and be able to commit that to a version control system. One of the last major projects I worked on there was a case where I had a few jobs on the go with the same client, and was always switching back and forth between the current version and the version that was either in production, or on the development machine. Now although I had control of the source code for the project the one thing that was lacking was the ability to version control the schema, that changed when Red-Gate provide the ability to save the schema as script files to a directory.

So how does this then work, well it is actually very simple. It compares the database schema with the scripts that are actually in the folder for changes and makes them as if it was a database. So with that in mind lets begin by creating a new project within SQL Compare, you'll notice that there are a few options available to you, but for now we are only interested in setting up as in the diagram below.

The first thing you need to do is setup the source and destination to the databases, I have the habit of always having my development always on the left hand side and as you can see in the diagram above, I have set it to the scripts folder. The only thing left to do on this side now is to tell SQL Compare where to place the SQL scripts, and we do this by selecting the folder that we want to place these scripts. In this example I have already got a directory structure for my project that includes the directory SQL scripts underneath the root of my main source files for my project.

On the right hand side we then create the connection to then connect to the database, that we will the be comparing too. Once this information has been setup we can then just hit the compare now button, and the database will be compared with the SQL files that we have told it to compare against. And as this is a new setup there is nothing to compare against, so the results will be that all the tables in the database will be different.

That's pretty much it and the thing is that you can do this as often as you like, to keep the SQL script files up to date. The only caveat to this approach is that you will need to make sure that the comparison, is actually made before you begin switching projects in your version control system.

One thing to keep in mind here to is that you can rebuild the database from these SQL scripts, or just migrate the database changes by comparing the schema back to the database by switching the direction that the comparison is made.

Please be aware that this is for schemas only.