Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    13

    Question Unanswered: Rollback stored procedure

    NEW TO THIS ROLL -- I'm trying to come up with a rollback plan for our SQL Server databases. We are constantly receiving patches and stored procedures from one of our vendors and we currently do not have a plan to rollback other than a full restore of the database.

    What is the best practice for a situation such as this?
    If a stored proc is run and it causes an issue what is the best way to rollback?

    Also, I am working on renaming our existing stored procs instead of dropping them when we receive updated procs as a way to revert back if we need to. Is this practical?
    Last edited by ChrysW; 08-08-08 at 14:54.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20080808  From http://www.dbforums.com/showthread.php?t=1632908
    
    DECLARE @cOldname	sysname
    ,  @cNewRoot		VARCHAR(20)
    ,  @cNewName		sysname
    
    SET @cOldName = 'Pat'
    SET @cNewRoot = 'Phelan'
    
    SET @cNewName = @cNewRoot + ' ' + Convert(CHAR(10), GetDate(), 121)
    
    EXECUTE sp_rename @cOldName, @cNewName
    -PatP
    Last edited by gvee; 08-11-08 at 17:32. Reason: Wrong URL in code

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ChrysW
    If a stored proc is run and it causes an issue what is the best way to rollback?
    Restore the database to a point in time prior to the execution of the sproc.
    This is what testing is for....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can also use database snapshot, but do not keep it around longer than a day. Your performance, if activity is relatively intense (1000+ batch requests/sec), may be degraded while the snapshot is in effect, but reverting to pre-patch state is much easier than anything else.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Mar 2008
    Posts
    13
    Is it a good idea to rename/save the old procs? I am renaming them to save the older version in case we need to revert back to them but is renaming or saving them the right thing to do?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Makes no difference as long as you have them available. I guess I would prefer not to crowd up my application with dead code, so it makes more sense to save them to a file, or check them into a code vault.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Dunno why MS never used "versioning" with sprocs/functions/triggers/views as they did with DTS packages.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I really can't see much use for versioning in sprocs. Database objects are so closely tied together that you would have to be extremely careful about rolling back a single sproc or table change.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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