Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to restore a stored procedure from a backup Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 9:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:44 PM
Points: 383, Visits: 568
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...
Post #1467235
Posted Tuesday, June 25, 2013 9:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1467247
Posted Tuesday, June 25, 2013 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1467252
Posted Tuesday, June 25, 2013 10:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:35 AM
Points: 702, Visits: 2,172
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1467266
Posted Tuesday, June 25, 2013 10:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:44 PM
Points: 383, Visits: 568
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...
Post #1467291
Posted Tuesday, June 25, 2013 11:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467294
Posted Tuesday, June 25, 2013 1:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1467366
Posted Tuesday, June 25, 2013 1:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:44 PM
Points: 383, Visits: 568
Thanks! I will give that a try.

There is an exception to every rule, except this one...
Post #1467368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse