What options are available for doing Source Control in SQL Server 2005 / 2008?

Question

Title says it all really. What software is available for enabling Source Control in SQL Server 2005 and above. What are the downsides of doing Source Control in SQL Server if any?

Answer 1

I prefer to not source control the database itself but the scripts I use to create the database. I am using Visual Studio's Database Edition (Which is now available as part of the developer edition).

Essentially the tool keeps all the create scripts in source control, but then you can generate scripts that will update a database to match a project. It works very well.

Answer 2

It's a tricky thing to solve. Since most databases I work against are tied to a specific application in Visual Studio (YMMV), I typically do this: once development has started, I generate a "create database" script for schema only and put it as a .sql script in some part of my application's project. I turn on the feature in SQL Management Studio that prompts me to create a change script every time I change a table or stored procedure or what have you...so whenever I do change something, I save out the change script and add that to the end of my original "create" script. That way anyone can grab the SQL script and completely generate a new copy of the database up to wherever the trunk is.

It's primitive, and needs more management around keeping the database changes in sync with the version of the app that hits it, but it's better than nothing for me. I'd be interested to hear if there are source control solutions that integrate more cleanly.

Answer 3

Well it depends on how you want to do it. You can use Visual studio 2005/2008 to generate database projects which will generate all the necessary scripts to create your database. You can then check the scripts into any version control system you like.

Toad also has a "team coding" feature which allows you to use toad as your version control system.

I would strongly recommend you get into the habit of using version control for your databases. Some of the benefits are highlighted by Jeff in his article on databases and version control. I have used this method with MSSQL databases and visual studio for versioning my databases.

Answer 4

The built-in Source Control in SSMS only allows you to handle scripts; it won't track changes to your DB directly.

Personally, I use ScriptDB to automatically created individual scripts for my entire DB, which is then source control through Visual Studio - as I check in my application code, it checks in these SQL scripts as well. I also script any changes to the DB explicitly - these are used to update production servers in due course - and these are also source-controlled via VS.

Pretty much everyone I've come across uses a similar technique if they use anything at all. Many don't.