[cvsnt] Managing SQL Server source with CVS?

[Glen Starrett]

> Does anyone know of any tools available that could ease version
> management of (MS) SQL Server sources? Our apps are for a large part
> written as stored procedures and triggers. Currently the way is to
> initiate an export to files and check those files into CVS.
> We use Enterprise Manager's built-in Generate SQL Script function to
[snip]

What I do is keep all my SQL source in a directory that is under CVS
control.  I have a script to post changed files to SQL (it takes a manifest
list so that only the desired files are processed).  I also have a script to
pull the files out of SQL server in the manner of SEM but it's a lot more
consistent (no click-click-click oops I forgot to set ANSI,
click-click-rename stuff).  Nothing too fancy--no integration with SEM, but
they work for me.

With my situation I have at 4 instances of my database:  My DEV, other dev's
DEV (in India), TEST, and PROD.  This allows me to flow changes out and into
the TEST/PROD very consistently.  

The way I'm using this:

--Initially I pull all SQL objects from my DB (tables, procs, triggers if
any, etc.)

--All object scripts have full DROP then CREATE syntax so they can be re-run
(*disadvantage for tables! See later step)

--As I update the database objects, I update my manifest for that release
(e.g. R120-updates.txt has the list of procs in it)

--For schema changes I put a "table changes" script at the head of that
schema that first looks for the new column or changed element then, if not
present, executes the change script to update the schema on that table.

Whenever an object is changed, I change it in DEV typically interactively
since that's a LOT faster to author / debug.  Then I update the object's
file in my file system & CVS with the update manifest and can run my MakeDB
script against TEST to make sure I didn't miss anything.  NO changes are
made directly in TEST to ensure that I have a good manifest for the later
PROD update.

I've never tried using my 'dump' script for 2-way updating, but I'd imagine
it would work fine since that is what creates the initial files anyway (it's
been a long while since I ran it though, so I don't know what state it's in
exactly).

I can email you the scripts if you like.  The only thing I'd ask in return
is if you could document your usage of it to the cvsnt wiki for other's
future reference :)  (optional, but a good karma step).

Glen Starrett