Database Modification History

  • dravenx

    Grasshopper

    Points: 15

    Our SQL Servers are MSSQL 200, sp3 and I have this question:

    Is  it possible to view the history of any changes to the database structure?  For example, if a field is added to a table, is it possible for me to determin this?  If so, how? 

    Thanks.

     

  • davidc@trinityapex

    SSC Veteran

    Points: 235

    Newbie,

    First, no one should be modifying the table structures, contstraints, procs or triggers, using the sa/dbo id.  Only their own id, and not in a production db.  On top of that there should be implementation procedures that safe gaurd you and yours asset.  Problem solved.

    I'm kidding.  S$%t happens.  There are a couple of ways to write the scripts yourself, simply placing generated ddl etc into a table or writing to a file and doing size and/or text compares.  But it may be too late.

    Using Profiler to write to a table to monitor events such as those you refer to, is fine, but should not be done in production either.

    I like to purchase simple tools that allow me to do my real job.  To that end, there are a couple of vendors/tools out there that can help you accomplish your goals, just at different stages of exposure.

    SQLDiff is a neat and inexpensive database comparison tool. It assumes that you have a static db to compare to.  It does all the neat color coding and script generation for you.

    SQLCompare will do what SQLDiff does and more, as it will compare the content also and sync data as well.

    If you wish to go further, and your real objective is to monitor a production environment, there are some tools out there that have 'zero impact' monitoring of the sql logs, and can subsequently alert someone of the 'breach'.

    Hope this helps a little. 

    David.

Viewing 2 posts - 1 through 2 (of 2 total)

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