Post reply

recovering stored procedures

  • March 14, 2006 at 4:06 pm

    #168163

    I recently had a series of issues occur while updating a very large table on a very large db (Total data more than 100 GB to give a frame of reference on its size) on what I'll call server #1. I was running an update over a weekend when a scheduled backup occurred. The following week I ran an additional update on the same very large table.

    A torn page later...

    Attempts to recover the db did not work, so we attempted to restore the backup on a separate machine (server #2), so the original server and other dbs on it could still be used. Whatever corruption issue that caused the initial failure occured for the weekend backup as well. We finally were able to restore the prior weekend's version.

    Here's the problem. Stored procedures were created and revised during the time between the torn page issue and the prior backup.

    Is there a way to recover those new and revised stored procedures from server #1?

  • March 15, 2006 at 8:59 am

    #626806

    The code for stored procedures is kept on the system table syscomments which resides on your database. To be honest, unless you have been using VSS or some scripting method to backup your sprocs automatically, I don't think you will be able to get them back.

    When in doubt - test, test, test!

    Wayne

  • March 15, 2006 at 10:03 am

    #626843

    Thanks.

    The syscomments table shows portions of text from the procs, but not complete versions. Doesn't seem to matter if I view it in grid or text mode.

    Is there something else I'm missing?

  • March 16, 2006 at 1:08 am

    #626953

    In Query analyser, go Tools/Options/Results. Change 'Max chars per column' to 8012.

    Large sprocs could be split over multiple rows in syscomments.

    When in doubt - test, test, test!

    Wayne

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

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