Versioning SQL Server DDL code

Question

I'd like to have all DB DDL code under CVS.

We are using Subversion for our .NET code but all database code remains still unversioned.

All we know is how important DB logic can be. I've googled but I've found only few (expensive tools). I believe there exists other (cheaper) solution(s).

What approach do you advise to follow? What tools are most appropriate?

SQL Server 2005, VS 2008 TS, TSVN

UPDATE Our coding scenario is that developers cannot access to PROD DB directly. It is changed only by scripts (so this is not a problem)

I'm mostly interested in the DEV environment where all of developers have full access.
So it happens that a developer overwrite USP previously changed by another.
I'd like to have the possibility to restore lost version / compare USPs revisions etc.

UPDATE-2
To create deployment script we are using Red-Gate SQL Compare.
Works perfectly - so deployment scripts are not a case.

Answer 1

If you haven't already read it, Martin Fowler's article Evolutionary Database Design is a great place to start.

The article is hard to summarize, but it describes how his team dealt with database versioning in a rapidly changing development process. They created their own tools to facilitate things: scripts to bring users up to the current master, to copy any version of the schema so users could debug one another's working copies, etc..

For a solid low-tech solution, I've found it helpful to keep two kinds of DDL scripts in source control:

• A master version that can create the database objects from scratch.
• 'Version upgrade' scripts for each development iteration.

They're redundant to a degree, but extremely useful (particularly when it comes to deployment).

Answer 2

If you haven't already looked at the Visual Studio Database Edition GDR (a.k.a. "Data Dude"), you should definitely download it and try it out:

http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Among other things, the GDR will facilitate team development by making it easy for each developer to maintain their own local copy of a database, version scripts, create deployment scripts to move a database schema to a new version, and even support database rollback.

It's free if you are using team system developer edition. Check it out.