how to restore a stored procedure from a backup

Question

how to restore a stored procedure from a backup

SQLHeap

Hall of Fame

Points: 3572
More actions
June 25, 2013 at 9:30 am

#279520

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...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

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.

"I cant 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."

-- Itzik Ben-Gan 2001

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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 SSCertifiable Points: 6726 More actions · Answer 3

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 Hall of Fame Points: 3572 More actions · Answer 4

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 SSC Guru Points: 323504 More actions · Answer 5

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 Hall of Fame Points: 3572 More actions · Answer 7

Thanks! I will give that a try.

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