Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to restore a stored procedure from a backup


how to restore a stored procedure from a backup

Author
Message
SQLHeap
SQLHeap
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 736
The db is too big to restore fully, can't even just restore the primary filegroup anywhere. How can I get a single stored procedure back?

There is an exception to every rule, except this one...
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2242 Visits: 7425
If you have db backed up as a single .BAK file then restoring the database is the only way I can think of; there is no way to dig around that file.

Note that you can restore it to another computer and grab the DDL for the stored proc from there.

It's worth noting that Redgate has a product (I believe it is SQL Backup Pro) that allows for object-level recovery. I have used it in the past for this kind of situation.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7444 Visits: 15128
Yes, I think LiteSpeed from Quest has object level restore as well.

Another alternative is to purchase some software that allows you to mount a database backup as a database without actually restoring it. I can't remember who makes such utilities.

If neither of the above is possible, you'll need to restore the whole database.

John
s_osborne2
s_osborne2
Right there with Babe
Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)

Group: General Forum Members
Points: 745 Visits: 2292
Hi,

You might be able to make use of the trial version of this:

http://www.apexsql.com/sql_tools_restore.aspx
http://www.apexsql.com/sql_tools_recover.aspx

Thanks,

Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
SQLHeap
SQLHeap
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 736
Thanks for the ideas, will try them. There are 5 files in 3 different filegroups, is there a way to just restore 1 filegroup at a time to a blank db somewhere?

There is an exception to every rule, except this one...
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14956 Visits: 38955
SQLHeap (6/25/2013)
The db is too big to restore fully, can't even just restore the primary filegroup anywhere. How can I get a single stored procedure back?


grab the trial version of Idera's SQL Virtual Database, which is one of the products that John Mitchell mentioned: it opens a backup as if it were a real database, but without the disk space requirements.

then you can grab and DDL definitions or data you need.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
YEs, you can restore just the primary filegroup from the backup, even if it's the full database backup.

Look up the RESTORE DATABASE syntax, you're looking for the FILEGROUP = option, and you'll want to specify PRIMARY as the filegroup to restore.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLHeap
SQLHeap
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 736
Thanks! I will give that a try.

There is an exception to every rule, except this one...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search