tarish.velivela (11/12/2007)
--------------------------------------------------------------------------------------------2. You must keep a copy of the SP somewhere else because when the master database gets rebuilt your system stored procedures will be gone. This could also happen when applying a service pack and/or during an upgrade to a different version of SQL Server.
--------------------------------------------------------------------------------------------
Is this true? if so, how can I overcome this issue? I will be having more than 100 or nearly 200 SPs this way. Even if I keep a copy of the queries used in these SPs, it will be a tedious job to create so many SPs again in future .
If you ever have to rebuild your master database (say, due to corruption), this is true. However, you should have a backup to apply after you get SQL Server up and running again. With respect to service packs, your own stored procedures aren't very likely to be affected. If you modify a system stored procedure belonging to Microsoft, that's a different story.
With that said, it's still better to stay clear of the master database as much as possible.
K. Brian Kelley
@kbriankelley