Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Question Unanswered: haunted database (reverting procedures)

    For the second time in two days, a completely rewritten procedure (using "alter procedure" statement) has reverted to an earlier version after hours of testing and confirmation. In both cases, there was 4-6 hours from successful final testing to discovery of the "reversion."

    Since sql server has no memory of earlier procedure versions, I can only conclude:

    1) someone restored an earlier version of the db (log says no)
    2) someone acidentally ran a script for the old procedure (no evidence for that)
    3) the database crashed and was rolled back as part of restore (but alter procedure is a ddl and, as such, is auto-commited so it cannot be rolled back)

    None of these 3 possibilities seems to have occurred. Is there another possibility I'm missing?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i vote for number 2 and number is likely if you do not have good change management in place.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    If you're using SQL Server 2005 I'd create DDL trigger and log all DDL actions to a log table.

    To restore what you have lost you could simply restore database and log backups to a point in time before your procedure was "reverted".

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or hopefully you are using source control and you can look at the previous version of the procedure, who changed it etc...

    in environments with lots of developers I like automating the database build straight out of source control and put the ability to change testing and production environments in as few hands as possible. all scripts that do get run on production should be kept in a dated change folder with documentation.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    Well, my face is a little red here because I'm the only person who could've inadvertantly overwritten the new proc with the old one. We don't use version control (I've not been thrilled with any of the source control solutions available for sql server 2000).

    Of course, no ddl triggers in sql server 2000, so kaffenils's good suggestion is not applicable (my fault for not specifying mssql2000 in my post).

    I have found other instances of reversion throughout the last year - but this is the first time it involved the same procedure two days in a row. The first day, two procs and an alter table statement were reverted (and there's NO way I had a create statement for the old table design to accidentally run), but the 2nd day only one of the two procs reverted. Very strange.

    Perhaps my db really is haunted.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by onansalad
    Well, my face is a little red here because I'm the only person who could've inadvertantly overwritten the new proc with the old one. We don't use version control (I've not been thrilled with any of the source control solutions available for sql server 2000).

    Of course, no ddl triggers in sql server 2000, so kaffenils's good suggestion is not applicable (my fault for not specifying mssql2000 in my post).
    Version control for SQL is not a patch on (for example) .NET but I think it far superior to nothing.

    Anyhoo - you could try running traces if these things are as regular as you say.

    Another possibility - you don't have any objects creating other objects do you?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •