undo changes to a stored procedure
-
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 a backup or source control, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
and that's why every database should be stored in source control just like application code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
and why every database should have backups, test/dev or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
maybe, but doubtful, the default trace might contain the textdata for the ALTER PROCEDURE command the other developer originally did...
but since the default trace rolls over so quickly, it probably only contains your command and not the other developers.
run the Schema changes report and see if you are lucky or not.
good luck.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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?
-
my mistake, the default trace does not capture the textdata, it seems; I assumed it did.
i created a new database, ran a script that added a table and a suite of procedures and functions.
while i see the create/alter events, the actual textdata of the commands used is not captured.
declare @TraceFileName nvarchar(256)
set @TraceFileName = (select path from sys.traces where is_default = 1)
-- get general name for Default Trace (remove rollover number)
set @TraceFileName =
substring (@Tracefilename, 1, (charindex ('\log_', @tracefilename) + 3)) + '.trc'
print @TraceFileName
select
ev.name ,
tr.*
from fn_trace_gettable(@TraceFileName, default) tr
join sys.trace_events ev
on tr.eventclass = ev.trace_event_id
join sys.trace_subclass_values sv
on tr.eventclass = sv.trace_event_id
and tr.ObjectType = sv.subclass_value
where tr.objectname is not null
and DATEADD(dd, DATEDIFF(dd,0,tr.starttime), 0) = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
On a side note. Do you guys know how to access the rdl file of the default reports so that we can edit them?
-
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 ?
-
homebrew01 (7/19/2011)
ps. Why do I have so much white space after my code ?Don't know, but I'm seeing similar issues with the articles that I've read recently.
Like select @@version will take almost half the screen.
-
What whitespace where? Can't see anything odd.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
-
I still can't see anything odd. No blank lines, no right-padding...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
That's odd, my screen shows a lot more than yours. Using IE8.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply