January 11, 2008 at 6:17 am
Is there any means to recover a stored procedure, over which an alter procedure script has been run...please help...thanks in advance.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 11, 2008 at 6:30 am
Not really.
You could restore a previous backup of the database to a different location and retrieve it that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2008 at 6:31 am
Get a backup of the database and run the following
select * from information_schema.routines where routine_name ='NAME OF YOUR PROC'
I'd also suggest some source control, we use Subversion and it's quite good but there are others.
Hope that helps
January 11, 2008 at 6:32 am
Actually, you could also get one of the third party log explorer tools and retrieve the older version of the procedure from the log IF you have your recovery set to something other than Simple.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2008 at 6:36 am
iain lastname (1/11/2008)
I'd also suggest some source control, we use Subversion and it's quite good but there are others.
I'd like to second this suggestion. You should treat the code in your database in the same way that developers treat their code. You should have it in some type of source control and management tool that allows you to version the scripts, label and branch. This allows you to not only recover an oopsie like deploying the wrong procedure, but you can reset your entire database to a point in time coordinated with particular databases. Invaluable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2008 at 7:05 am
yes...I am now thinking of using a source control.....we use VSS for our .Net codes....can you suggest any similar source control for SQL, which can automatically keep track of the changes, i.e., versioning
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
January 11, 2008 at 7:21 am
Subversion was recommended above. I've used VSS in the past. I'm currently using TFS, Team Foundation System, the new management tool from Microsoft.
Picking the code management tool is the easy part. The hard part is implementing it. You may want to look into getting another tool to help with the management. We're using Team Edition for Database Developers through Visual Studio. There's also DBGhost and Red Gate's SQL Compare that I know work with version control systems. There're probably others.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply