I guess the issue here is 'you get what you pay for' - this is a great article and provides an excellent (and free!) way to version your databases.  You could even do a little more scripting and push the scripts into a SourceSafe project incrementally every night. i.e check everything out and then overlay the scripts on disk with the newly scripted DB.  Then check it all back in, add and new scripts and delete any that are now missing and you have an audit trail of what changed on your development/test/production database that day.

However, this won't tell you who changed things and, crucially, why.

For a full change management process for SQL Server code I recommend you take a look at DB Ghost (www.dbghost.com).  It can compare databases, build databases from drop/create scripts under source control (with no dependency issues) and can script out entire databases (including permissions!).  All this can be run from the command line as well as the UI to acheive a fully automated process.

Having the developers modify the drop/create scripts in source control means that the DB Ghost Process is perfectly aligned with configuration management best principles (who changed what, why and when) and is therefore also a huge step towards Sarbanes Oxley / HIPAA / BASEL II compliance etc.

Once you've used it you'll wonder why no one thought of it before.

- Malcolm

One thing I have noticed with SCPTXFR is that it changes the name of most of my Primary Keys. Example:

Source database PK Index:  PK_tblBox_BoxID

Dest database PK index: PK__tblBox__1A69E950

I'd like to retain the descriptive names. Does anyone know a way to accomplish this?

Chris

I don't unfortunately Chris. However, from memory, I think it's replacing the name of the primary key with its objectid. Quite why MS did this I don't know.

Jon.

Thank you for the article; very helpful.

Also a minor discrepancy:  In the introduction to Real World Examples it states "NB: All examples use a database server called DEVSVR." but some examples use svdb3