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 |
|
|
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 |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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.DanielSQL Server DBA |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-24 : 22:36:22
|
There are free Version Control utilities ... of which I think CVS is oneKristen |
|
|
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 |
|
|
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 |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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 |
|
|
|