June 10, 2009 at 1:52 pm
I have a need to separate stored procs from the tables / data. How can I create a new filegroup with procs only, or move the procs to a new filegroup, or store the procs separately from the data?
Any suggestions?
I would prefer to keep the procs in the same database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2009 at 1:59 pm
Stored procedures are stored in one of the system tables. The system tables are all on the primary filegroup and cannot be moved. You could create a second filegroup for the user tables and have just the system tables on Primary, it'll kinda do what you want.
May I ask the reason for this requirement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2009 at 2:03 pm
I considered moving all tables already. In fact we are already in process of doing that. The only issue with it that I see is that it doesn't quite meet the needs. The reasons for the requirement are purely political / contractual. We would like to be able to restore data without procs - filegroup backup/restore pretty much accomplishes that for us - just not the proc piece.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2009 at 5:16 pm
jason brimhall (6/10/2009)
I would prefer to keep the procs in the same database.
I know you'd prefer to keep the stored procedures and tables in the same database, but it might be easier to create another database and move the stored procedures there. you'd be able to backup and restore the "table" database while leaving the stored procedures alone. You'd have to enable cross-database ownership chaining on the two databases.
Greg
June 11, 2009 at 9:05 am
Thanks - that is the route we decided to take. Much more time consuming but is the best in the end.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply