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.

Guipure

Answer 1

Finally I found this tool and approach extremely useful and very easy to introduce
(at least at the beginning - where no versioning solution on the place):

http://www.codeproject.com/KB/database/SQLScripter.aspx

You can run it out of the box.
For final solution I'd incline to GDR.

Guipure

Answer 2

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).

Cambridge

Answer 3

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.

Remittent

Answer 4

If you are using Visual Studio Team Suite or Visual Studio Developer Edition, you are entitled to a copy of Visual Studio Database Professional. This is designed to do exactly what you describe, and much more. We use it to manage our database schema (code).

Randy

Resupine

Answer 5

We use Subversion for all our database code as well. Since nothing is allowed to go to Prod unless it is in a script, there seems to be no porblem with getting people to put all the scripts into subversion. We tend to write alter table scripts to change tables with existing data and then recreate the whole table structure in case we need to create a new database from scratch (we often have the same database structure on multiple servers as some of our clients are very large and do not want their data accidentally available to the competition and so pay for separate servers and therefore may need to create the whole database again with no data.) For objects that don't directly store data we drop the orginal object and recreate it with a create statement. Each project has it's own home inthe repository and each database does too, so the script may be in more than one place to facilitate deployment.

But the real key is that no one can load to Prod without a script. We don't give our devs direct rights to prod, so they have no problem doing things in scripts as opposed to using SSMS.

Contravallation

Answer 6

This also sounds interesting:

Freeware:
http://dbsourcetools.codeplex.com/
http://www.codeproject.com/KB/database/ScriptDB4Svn.aspx
http://www.codeproject.com/KB/database/SQLScripter.aspx
http://blog.boxedbits.com/archives/133

Commercial:
http://www.nobhillsoft.com/Randolph.aspx

Guipure

ScriptDB4Svn requires sql2000's scptxfr which does not support 2005 object types. The lack of scptxfr in 2005+ made me write my script tool. – Sorensen

Answer 7

I wrote SMOscript which generates a CREATE script for each object in a database.

Use this tool to generate into a directory covered by CVS, and update your repository.

Sorensen

Answer 8

You should use Management Studio (SSMS) and place the .sql under source control, possibly separate schema objects under folders. Hope this helps

Batholomew

I have tried that. But in small team this consumed too much time. So we decided to automate that – Guipure

Answer 9

See if Wizardby fits your needs.

Valenzuela