Database structure and source control - best practice

Question

Background

I came from several years working in a company where all the database objects were stored in source control, one file per object. We had a list of all the objects that was maintained when new items were added (to allow us to have scripts run in order and handle dependencies) and a VB script that ran to create one big script for running against the database.

All the tables were 'create if not exists' and all the SP's etc. were drop and recreate.

Up to the present and I am now working in a place where the database is the master and there is no source control for DB objects, but we do use redgate's tools for updating our production database (SQL compare), which is very handy, and requires little work.

Question

How do you handle your DB objects? I like to have them under source control (and, as we're using GIT, I'd like to be able to handle merge conflicts in the scripts, rather than the DB), but I'm going to be pressed to get past the ease of using SQL compare to update the database.

I don't really want to have us updating scripts in GIT and then using SQL compare to update the production database from our DEV DB, as I'd rather have 'one version of the truth', but I don't really want to get into re-writing a custom bit of software to bundle the whole lot of scripts together.

I think that visual studio database edition may do something similar to this, but I'm not sure if we will have the budget for it.

I'm sure that this has been asked to death, but I can't find anything that seems to quite have the answer I'm looking for. Similar to this, but not quite the same:

What are the best practices for database scripts under code control

---

Started a bounty, as I'm interested in canvassing for a few more opinions - the answers here are sound, but I feel that there should really be an easier way.

Thanks for all the great answers - all have their merits, so I'm going to take the highest vote, but cheers for all the input.

Answer 1

Have a look at this five part series on the principles and practices of database version control (by K. Scott Allen):

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

The five parts are important but basically the idea is to have a baseline and then change scripts (with a version table). Updating the database means applying change scripts "above" the current version. And this strategy is very VCS friendly (no conflicts).

Answer 2

We have all our database objects under source control using Visual Studio Database Edition (DBPro). It is a wonderful tool that version controls our schema, does builds, validations, allows code analysis, schema comparisons, deployments, data comparisons, refactoring etc. It was designed from the ground up to be a DB management and version control system. Highly recommended.

This is the blog site of the lead architect for DBPro: click here