Poor mans approach is to version the updates and version the database. (Im
sure theres way more sophisticated tools that would do all this form you
mayby like dbghost etc)
So in your update script you may have something like
SET @CurrentVer = (Select dbschemaVersion from ControlTable)
If @CurrentVer != @ThisScriptVer -1
Begin
--Notify database is wrong version
END
ELSE
BEGIN
BEGIN TRANS
--Perform schema update (or drop and create)
Update ControlTable SET dbSchemaVersion = @ThisScriptVer
COMMIT TRANS
END
You guarantee your script can't be applied unless it is in the right
sequence, of course you will have to figure out the best approach to version
your schema.
On Mon, May 31, 2010 at 11:14 AM, Greg Keogh <[email protected]> wrote:
> Folks, I’m sure we’ve all had problems where multiple developers change
> SQL Server scripts and they get out of whack and waste time with stupid
> errors.
>
>
>
> I’m starting a fresh app and I thought I’d experiment with keeping scripts
> in SVN. It just means that we have to remember to always save a script to
> the source controlled file whenever it’s changed.
>
>
>
> Because scripts aren’t compiled source code, there is still the risk of
> human error in not pushing any updated script files into the DB. I was
> thinking of concocting a utility which automatically pushed changed scripts
> into the DB, but before I start fiddling I thought I’d ask about this
> subject in general first. Are there others out there who source control
> their DB scripts and have techniques for reducing human error? Or perhaps
> there are better techniques that I’ve completely overlooked.
>
>
>
> Greg
>
