| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2006-04-18 : 13:59:59
|
| Hi!One of our guys replaced several custom stored procedures by older version ones.Where are these SPs stored? I want to use my backup to get them back. Please help! Thanks! |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-04-18 : 14:13:35
|
| If the database is relatively small, you can restore the database under a different name or server, and generate the scripts from that database. No history of stored procedures is kept in SQL Server. |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-04-18 : 14:19:08
|
| I found their names in sysobjects table of my current database. Can I restore an older backup to another server, then bring that table into my production live? Where are the T-sql in the sp stored? |
|
|
|
suhde76
Starting Member
10 Posts |
Posted - 2006-04-18 : 19:04:45
|
| Hi Hommer,sysobjects system table contains information about the objects in a database, ie, tables, views, stored procs, etc. Definition of the stored procs are stored in another system table called syscomments.What you can do is to restore an older backup on a test machine and then bcp the syscomments table to the original server. But be careful, double check if the id of the stored proc in sysobjects in the existing table match that in the syscomments table you want to restore. If someone dropped the stored procs and then recreated them, then the IDs will be different and BCP will fail, fail in the sense that there will be invalid entries, and it will not serve your purpose.Thanks, Suhas, MS SQL Server Engineer, Microsoft. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 19:08:14
|
| Hommer,Why not restore the database using a different name (so as not to overwrite your current database), script out the stored procedures from the new database using Enterprise Manager's Generate SQL Script wizard, then run this script on the current database? This is what mcrowley recommended as well.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-19 : 00:51:04
|
| "What you can do is to restore an older backup on a test machine and then bcp the syscomments table to the original server."Sorry, but whilst this might be fine for an expert IMO I think its madness for anyone else, myself included!Edit: Now I suppose I'll find that Hommer's Sprocs are encrypted and this is the only way!Kristen |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-04-19 : 09:20:38
|
| Thanks everybody!To be honest, when I posted my question I did not recall where these SPs were stored. I was hopping they are in master or msdb so it will take shorter time to restore. After we found them and restored to another server, the issue became to identify the changed ones. Tara’s approach will work if we know exactly which ones need to be worked on. Otherwise, we were thinking to replace all of them. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-19 : 11:18:52
|
| You could script the restored database, and the "Live" one, and then compare the two (some sort of Programmers Editor which has a text comparison tool will do the job).IF - might be a big If! - you always Drop and Create your SProcs, rather than using Alter, then the date when it was last created will be in the System tables, so that would tell you what was changed, and when.Kristen |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-04-19 : 18:29:38
|
| Try the trial copy of AdeptSqlDiff ToolFrom http://www.adeptsql.com/download.htmIf 2000 I would use asqldiff170r29.exe, if SQL 2005 you will need to use the Beta version.Tim S |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-21 : 14:20:24
|
| What about a compare of the TEXT field from both sysobjcts tables? Wouldn't that work? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-21 : 14:52:36
|
| "sysobjcts tables"syscomments would help you with the differences, but to be honest the way the data is stored in there (pseudo line breaks at 255 char width, follow-on-pages once a certain size is exceeded, etc.)( it would be hard to write a really useful tool that way - unless you are planning to have this type of cock-up pretty often!Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-21 : 15:03:25
|
| I was just thinking that no matter what the difference is, they would instantly know that something in the proc had changed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-22 : 05:58:49
|
| That's certainly true, good point.In case it helps: a JOIN from syscomments to sysobjects on the [id] column would get the [name]Kristen |
|
|
|
|
restore stored procs