Post reply

undo changes to a stored procedure

  • July 19, 2011 at 5:22 pm

    #1355238

    mtillman-921105 (7/19/2011)

    That's odd, my screen shows a lot more than yours. Using IE8.

    Same here...

  • July 19, 2011 at 7:01 pm

    #1355271

    mtillman-921105 (7/19/2011)

    GilaMonster (7/19/2011)

    What whitespace where? Can't see anything odd.

    I think he's talking about extra lines in the code block rather than to the right of the code.

    Yes ... maybe specific to my browser (IE 8) ?. Sorry for the sidetrack.

  • July 20, 2011 at 5:56 am

    #1355498

    homebrew01 (7/19/2011)

    mtillman-921105 (7/19/2011)

    GilaMonster (7/19/2011)

    What whitespace where? Can't see anything odd.

    I think he's talking about extra lines in the code block rather than to the right of the code.

    Yes ... maybe specific to my browser (IE 8) ?. Sorry for the sidetrack.

    It's not a sidetrack. It's been bugging me for weeks, at least, and I'm certainly not the only one on IE8.

  • July 20, 2011 at 6:04 am

    #1355500

    homebrew01 (7/19/2011)

    Ninja's_RGR'us (7/19/2011)

    homebrew01 (7/19/2011)

    In addition, I run a job to script out objects to a folder every week as another layer of protection. If I need to recover stored procedure code, it's simpler than having to restore an entire DB.

    Agreed, but that too depends on restore time. Our restore is fully scripted to dynamic PIT and takes only seconds (well minutes during prod hours).

    Care to share the weekly export scripts?

    I have a table name with all my production databases in it that I loop through. For each one I run this:

    --- Script out Database objects

    set @code = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\scptxfr.exe" /s DatabaseServerName ' + ' /d ' + @DatabaseName + ' /I /F '

    + '\\FileServerName\SQLBACKUP\DatabaseServerName\Object_Scripts\' + @DatabaseName + '_structure_'+ convert(varchar(8),getdate(),112) + '' + ' /q /A /r'

    --select @code

    EXEC master..xp_cmdshell @code

    This is in SQL 2005, but at the time I built this, 2005 was missing some files, so I copied them in from my 2000 box:

    scptxfr.exe

    scptxfr.rll

    scriptin.exe

    sqlresld.dll

    Not sure what the best approach would be now. I've barely ever made use of it, and I manually delete old files every now & then. I should automate that.

    ps. Why do I have so much white space after my code ?

    Those where dropped from 2K5 and I don't have 2K. Can you zip those files up and upload them here? I'm not really trusting google on this one :w00t:.

  • July 20, 2011 at 8:29 am

    #1355671

    Ninja's_RGR'us (7/20/2011)

    homebrew01 (7/19/2011)

    Ninja's_RGR'us (7/19/2011)

    homebrew01 (7/19/2011)

    In addition, I run a job to script out objects to a folder every week as another layer of protection. If I need to recover stored procedure code, it's simpler than having to restore an entire DB.

    Agreed, but that too depends on restore time. Our restore is fully scripted to dynamic PIT and takes only seconds (well minutes during prod hours).

    Care to share the weekly export scripts?

    I have a table name with all my production databases in it that I loop through. For each one I run this:

    --- Script out Database objects

    set @code = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\scptxfr.exe" /s DatabaseServerName ' + ' /d ' + @DatabaseName + ' /I /F '

    + '\\FileServerName\SQLBACKUP\DatabaseServerName\Object_Scripts\' + @DatabaseName + '_structure_'+ convert(varchar(8),getdate(),112) + '' + ' /q /A /r'

    --select @code

    EXEC master..xp_cmdshell @code

    This is in SQL 2005, but at the time I built this, 2005 was missing some files, so I copied them in from my 2000 box:

    scptxfr.exe

    scptxfr.rll

    scriptin.exe

    sqlresld.dll

    Not sure what the best approach would be now. I've barely ever made use of it, and I manually delete old files every now & then. I should automate that.

    ps. Why do I have so much white space after my code ?

    Those where dropped from 2K5 and I don't have 2K. Can you zip those files up and upload them here? I'm not really trusting google on this one :w00t:.

    Here you go.

    There may be a native 2005 method to script now, but I have not looked into it.

    I put them here: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

  • July 20, 2011 at 8:35 am

    #1355682

    Thanks, I'm sure it'll help a lot of folks from now on.

    I've hit google with this and this feature was whacked from 2k5+. SMO is the only replacement which I have no intention to learn ATM.

  • July 21, 2011 at 2:03 pm

    #1356721

    Raphra (7/18/2011)

    I altered a stored procedure and unknowingly overwrote some changes that were made to it by another developer. Is there a way to undo the changes and get the old script back?

    Unfortunately I do not have a backup of that database, so that option is ruled out.

    Without regularly scheduled backups, attempting to restore would be problematic and probably not worth time or risk. Before attempting that or giving up, first try this quick attempt at gleaming the source for prior version of the stored procedure from the transaction log.

    #1 Locate a Hex editor that can handle large files; Notepad won't cut it, but HxD can be downloaded from web for free.

    #2 Stop the SQL Server service

    #3 Locate the transaction log file for the database in question. It's probably something like the database name with the extension .LDF. You can get the path looking at database properties. If you can't find it, then try searching for all files on the drive(s) with extension .ldf and modified today.

    #4 Using the Hex editor, open the .ldf file and do a text string search on all references to the stored procedure name. You may actually locate the text of the previous version somewhere in there, hopefully all in one piece.

    #5 After you're finished searching, then remember to re-start the SQL Server service.

    Another cool thing that HxD will do is open a disk, rather than a file, and do text searches across all disk sectors. Give it a shot, it may just work.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 7 posts - 16 through 21 (of 21 total)

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