restore a stored procedure only from .bak file

[jcaulder]

Just wondering, is there a way to restore JUST a stored procedure from a full database backup. The full backup is 9 GB so I don't have room to restore the entire database. Besides, all I want is the stored procedure.

Doesn't the code for the stored procedure get stored in one of the 'sys' tables in the database instance? If so, can I just restore that specific 'sys' table somehow to an existing TEST database instance and extract the code so I can rebuild the stored procedure.

TIA!

 

[fluteplr]

Unfortunatly not. Since SQL 7 restores have been an all or nothing thing. You can't do a partial restore of just a table.

Not that this will help you now, but you might want to periodically script off the schema of your database and archive the scripts. That way you will have all the code, etc. available without the size of saving all the data.

 

[ClaireHCM]

you can find the codes for stored procedure in master.dbo.syscomments table--text column .

But if you have a full database backup, I dont know how you r going to restore only the syscomment table. Better directly import from database instead of backup file

 

[jcaulder]

If it's in the 'master' table, that is a very small table. I can restore that one to a test environment with no problem. I was thinking I would have to restore the user database where the stored procedure was defined(which is 9GB compared to the master table which is only 16KB).

I will try this. That should me around the problem.

Thanks!