SQL Server Performance Forum – Threads Archive

Where are UDF and SPs?

The plan that my manager suggests to replace our old server is like: 1. Implement an exact SQL-Server as our production on new machine.
2. Testers test on new machine and it may take 2 or 3 weeks.
3. After Testers finished, I will get a backup from current databases in production
4. Old server will be replaced by new one and I will restore backups. My question is that if there is any UDF or stored procedure update in production during 2 or 3 weeks that Testers are testing, then would I lose that update by just RESTOREing the databases? In other words, where UDF and SPs are located? In the database or in system databases – master or msdb?
CanadaDBA

---

UDF’s and SP’s are stored in the db. So no need to worry here. Jobs and DTS packages are stored in MSDB.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
http://support.microsoft.com/default.aspx?scid=kb;EN-US;261334
might also help —
Frank
http://www.insidesql.de

---

Hey Frank:
I don’t think we change any Jobs or DTS packages or even add/remove users during those weeks. Therefore, I think I am in safe side. Thank you for the useful links.
CanadaDBA

---

One other thing that you need to consider (if you haven’t already) is that unless you create the users on the new server maintaining their SIDs from the old server, you will have orphaned users when you restore the databases. Gonna put you to the test here…
See BOL for sp_addlogin on how to create the logins taking the SID from the old server.
See BOL for sp_change_users_log on how to fix orphaned users if you don’t transfer the SIDs. Steve

---

Hi Steve [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

---

Hi, Frank! Steve

---

]]>