SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Restore deleted stored procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mmats
Starting Member

47 Posts

Posted - 01/24/2005 :  11:45:22  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 01/24/2005 :  13:56:39  Show Profile  Reply with Quote
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

USA
36823 Posts

Posted - 01/24/2005 :  15:00:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
4184 Posts

Posted - 01/24/2005 :  19:45:10  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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

USA
752 Posts

Posted - 01/24/2005 :  21:45:04  Show Profile  Reply with Quote
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

Edited by - SQLServerDBA_Dan on 01/24/2005 21:45:41
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/24/2005 :  22:36:22  Show Profile  Reply with Quote
There are free Version Control utilities ... of which I think CVS is one

Kristen
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 01/24/2005 :  22:43:25  Show Profile  Reply with Quote
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


Edited by - eyechart on 01/24/2005 22:45:26
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/24/2005 :  23:01:06  Show Profile  Reply with Quote
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

USA
4184 Posts

Posted - 01/24/2005 :  23:32:18  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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 Posts

Posted - 03/27/2013 :  10:42:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000