Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Restore deleted stored procedure?

Author  Topic 

Mmats
Starting Member

47 Posts

Posted - 2005-01-24 : 11:45:22
Is there any way to restore a single stored procedure from a backup of a database? An SP was accidentally deleted and I dont want to have to restore the whole db

Kristen
Test

22859 Posts

Posted - 2005-01-24 : 13:56:39
Don't think so.

Restore the backup to a "new" temporary database, extract the SProc, and then DROP the temporary database?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 15:00:54
Stored procedures should be checked into some sort of version control software. If you aren't doing this yet, start doing it. Then you won't have to restore the whole database just to get one object out. We use VSS and ClearCase here (depending on the project).

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-24 : 19:45:10
We also use VSS. Do what Tara and Kristen said. Also, you might want to check out tools like Lumigent Log Explorer and Imceda LiteSpeed. They will allow you to restore individual objects from backup.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-01-24 : 21:45:04
quote:
Originally posted by tduggan

Stored procedures should be checked into some sort of version control software. If you aren't doing this yet, start doing it. Then you won't have to restore the whole database just to get one object out. We use VSS and ClearCase here (depending on the project).

Tara



Or if you're a extremely small company that isn't in the black yet and can't afford fancy stuff like VSS, keep each proc you create in an t-sql script. To some companies $500 might be a stretch... for instance someone with a home business large enough to need SQL Server (MSDE) but too small to have lots of expenses. Who knows.

Daniel
SQL Server DBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-01-24 : 22:36:22
There are free Version Control utilities ... of which I think CVS is one

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-01-24 : 22:43:25
there is no backup tool for SQL Server that will allow you to restore a single object from the backup. SQL Litespeed does not have this capability either. What you are looking for is a capability like Oracle's Export/Import utility. Unfortunately, that does not exist in the SQL Server world (yet).

There are some inexpensive tools available that can help you avoid this problem in the future. Imceda also has a tool called sql ide that uses SQL Server as a repository for their Query Analyzer replacement. Seems to work pretty well. Although I am somewhat hesitant to recommend their products after they falsely represented themselves on this forum.

remember this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37772


-ec

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-01-24 : 23:01:06
SO how much is it then? Always amazes me when I can't find a price on a web site. Maybe I can easily afford it - but now they, and I, will never know ...

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-24 : 23:32:18
They didn't ever apologize for that did they????

And, you're right. They only allow you to restore tables. We use both products at work. I forgot about that post from Imceda though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

CarolB
Starting Member

1 Post

Posted - 2013-03-27 : 10:42:48
You can restore a single SP from a database backup even without restoring the backup. You need a tool for comparing and synchronizing object schemas (such as ApexSQL Diff unspammed). Set the database backup as a source, the live database as a destination, filter only the stored procedure you want to restore and that's it
Go to Top of Page
   

- Advertisement -