Post reply

what is the best way to track DDL/Schema changes?

  • June 20, 2008 at 11:10 am

    #122920

    Hi there,

    We currently use a scheduled SQL job to run a vbscript that basically allows us to record what changes were made to a given server for tables,procs,views,etc into Microsoft Visual Source Safe. It is strictly for auditing purposes and change control. It works.

    The problem is we are doing away for VSS in favor of going to Team Foundation Server and as such can no longer use the little hack vbscript that we did use. Admittedly we COULD continue to use VSS and just have two seperate source/change control systems.

    Does anyone else happen to use TFS and do you have a solution to this or are there other 3rd party tools that would be recommended?

    Thanks,

    Chris

  • June 20, 2008 at 11:17 am

    #831956

    You may be able to do something with the Default trace. The default trace tracks schema changes (it's the basis for the Schema Change History report, which might also be useful). I'd imagine that if you were to customize that appropriately, you could have your own trace record the actual schema changes applied to a server (which would then capture the actual statements occurring). You would just have to have a regular process to extract that data for check-in.

    Otherwise - there's a similar feature in Visual Studio Team Suite for Database Pros (a.k.a), allowing you to compare the schema recorded in a given project, against the current DB schema, and generate a change script. You'd have to check that one, becuase I think it's going to assume the PROJECT is the "more current", and it sounds like you want to do the reverse.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • June 20, 2008 at 11:41 am

    #831966

    What about DDL Triggers ?


    * Noel

  • June 20, 2008 at 11:46 am

    #831968

    For auditing DDL changes I recommend using Event Notifications. I wrote about this in another thread.

    http://www.sqlservercentral.com/Forums/Topic517766-146-1.aspx

    I have no knowledge about integration with TFS.

    Ola Hallengren

    http://ola.hallengren.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply