October 5, 2016 at 2:26 pm
I was working in the "Modify" script of a stored procedure just now. I accidentally selected only a small part of the code of the script, then ran it as a test, and in the process overwrote the rest of the procedure with only the selected code I had just run. I'm not the one who created this procedure, so I have no idea what went into it or how to recreate it; all I know is that it was a long, complicated procedure that I need to get back as soon as possible.
Can anyone explain to me how I might do this? This is urgent! I'm working on a consultancy and I will probably be let go if I can't rectify this in the next 24 hours.
Thank you.
October 5, 2016 at 2:30 pm
deekadelic (10/5/2016)
I was working in the "Modify" script of a stored procedure just now. I accidentally selected only a small part of the code of the script, then ran it as a test, and in the process overwrote the rest of the procedure with only the selected code I had just run. I'm not the one who created this procedure, so I have no idea what went into it or how to recreate it; all I know is that it was a long, complicated procedure that I need to get back as soon as possible.Can anyone explain to me how I might do this? This is urgent! I'm working on a consultancy and I will probably be let go if I can't rectify this in the next 24 hours.
Thank you.
Restore a backup of the database into a test environment and get it from there.
Retrieve a copy from source control.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2016 at 2:32 pm
If you haven't closed the window, use Ctrl+Z until you undo all changes. Then run the code to restore the procedure to its original code.
If not, follow Phil's advices. Good luck.
October 5, 2016 at 2:36 pm
Surely this procedure exists elsewhere. Here are a few places to look:
- Source code control
- Database backups
- Local copies of this database on developers' computers
- Copies of this database on other test or development servers
- Deployment scripts or dacpac files that were originally used to deploy the database
- The production database
Most development environments will have one of these, but if you're not familiar with the environment, you probably have to ask.
If the environment you are working in has none of the above, they have bigger issues than a mangled stored procedure.
October 5, 2016 at 2:36 pm
Luis Cazares (10/5/2016)
If you haven't closed the window, use Ctrl+Z until you undo all changes. Then run the code to restore the procedure to its original code.If not, follow Phil's advices. Good luck.
Hmm, good point. If you have Redgate SQL Prompt or SSMSBoost installed, there are additional recovery options too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2016 at 2:37 pm
So if I'm understanding you correctly, I should restore the DB to a test location, that way I can pick the code for the one procedure in question, recreate it in the live database, and then not have to worry about losing any data that was loaded since the date of the backup?
Also, is it possible to restore only the procedure in question, or only the full set of stored procedures for the database; or must we restore the entire database?
October 5, 2016 at 2:40 pm
Since I'm not the administrator of the database, I don't know what additional tools have been installed. How can I determine whether or not I have Redgate SQL Prompt or SSMSBoost?
October 5, 2016 at 2:46 pm
deekadelic (10/5/2016)
Since I'm not the administrator of the database, I don't know what additional tools have been installed. How can I determine whether or not I have Redgate SQL Prompt or SSMSBoost?
Those are client tools, so you should have them on your computer.
Changing that procedure shouldn't be something bad if it was done on a development/test server, just approach your DBA. If it was on a production server, approach a DBA immediately.
Through native tools, you need to restore the whole database (should be done as a different db). Though third party tools, you could be able to restore a single object.
October 5, 2016 at 2:53 pm
I think I will request the DB restore to a test server. That seems like the "easiest" thing to do.
Thanks, everybody, for the quick and thoughtful responses.
October 19, 2016 at 11:59 am
Before going through the time and trouble of restoring the database just for a single object, the following is worth a shot. What will do is search for any prior versions of the stored procedure in the plan cache.
SELECT st.text
FROM sys.dm_exec_procedure_stats AS d
outer apply sys.dm_exec_sql_text (d.sql_handle) as st
where st.[text] LIKE '%CREATE PROCEDURE%MyProcName%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 19, 2016 at 12:05 pm
As it happens, I just got the restore this morning; so even if it wasn't the most efficient, server-load-friendly solution, it did do the trick.
However, I am going to keep that code you gave me, in case this happens again.
Thanks!
PS- I'm not sure how to mark this thread as resolved.
October 19, 2016 at 12:08 pm
deekadelic (10/19/2016)
As it happens, I just got the restore this morning; so even if it wasn't the most efficient, server-load-friendly solution, it did do the trick.However, I am going to keep that code you gave me, in case this happens again.
Thanks!
PS- I'm not sure how to mark this thread as resolved.
You don't, except perhaps by saying it work and thanking those that helped (just like you did).
Others may come by later and offer other suggestions, that is the nature of ssc.
October 19, 2016 at 12:16 pm
Noted, for future threads.
Thanks again
October 19, 2016 at 12:23 pm
It's good that you have database backups. However, you also need to script out your database and maintain it a version control system. In addition to recovery of objects, it's also useful for change history documentation and version comparison.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 19, 2016 at 12:46 pm
Also, you aren't really doing work on a production system, are you??
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply