March 9, 2009 at 9:10 am
I need to create (or alter ) a stored procedure to be placed in a non-primary file-group. The reason for this is our database is periodically restored from production.
While I develop new stuff (tables, stored procedure, etc) I'd like to place them into another, non primary file-group , periodically back it up as well, and whenever prod database (which is only in primary) is restored I will follow with my restore.
To place tables, indexes is not a problem, but how about stored procedures ?
Thanks.
March 9, 2009 at 9:13 am
Stored procedures and other code objects are, to my knowledge, always in the Primary filegroup.
Shouldn't you have a separate dev and test database (preferably one for each of those) where you build new procs, so that, by the time they get to the production server, you already want them in the backups?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 9:29 am
GSquared (3/9/2009)
Stored procedures and other code objects are, to my knowledge, always in the Primary filegroup.
Yup. They're stored in the system tables and the system tables are always in PRIMARY.
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
March 9, 2009 at 10:49 am
The solution that I usually implement when I have a customer or situation that demands this ability (which is to emulate transportable-tablespaces from Oracle) is to actually put the stored procedure and any other objects that I want protected in this way into a separate database. Then I use automatically generated views (or synonyms) in that other database to make it appear to the stored procedures that they have the same tables local to them. Works great for me.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2009 at 11:40 am
I second RBarryYoung's solution.
That's also how we do it if we want to have our own procedures applicable for 3th party databases.
You could use synonyms in your own db to point to the 3th party db.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 9, 2009 at 12:06 pm
Another way to sort of achieve this would be to leave them in the database, but put all the stuff you want separate from them into a filegroup other than primary. Tables, indexes, in secondary (or whatever you want to call it).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 12:08 pm
RBarryYoung (3/9/2009)
The solution that I usually implement when I have a customer or situation that demands this ability (which is to emulate transportable-tablespaces from Oracle) is to actually put the stored procedure and any other objects that I want protected in this way into a separate database. Then I use automatically generated views (or synonyms) in that other database to make it appear to the stored procedures that they have the same tables local to them. Works great for me.
Actually had a "business requirement" that data not be kept in the same database as code, at one point. Each "application/solution" had its own database of procs, all of which were cross-database to the data store. Web pages used one database of procs, Access app used another, and so on.
It worked, but it did have a performance hit. About 3% if I remember correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 12:33 pm
SQL Guy (3/9/2009)
I need to create (or alter ) a stored procedure to be placed in a non-primary file-group. The reason for this is our database is periodically restored from production.While I develop new stuff (tables, stored procedure, etc) I'd like to place them into another, non primary file-group , periodically back it up as well, and whenever prod database (which is only in primary) is restored I will follow with my restore.
To return to the original question, I think I should point out that the SOP for your situation is actually to script out your procs before the restore, and then apply the script to recreate them.
Normally, it's only when you have to recover data-containing objects (tables) that you need the more complicated solutions that we have been discussing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2009 at 11:51 am
Thanks all.
This is what I do. I put all tables in another (non-prinmary) filegroup. But since I can't put stored procedures there I script them out. All of these objects (both tables, stored proces, etc) I keep in a separate (non-dbo) schema. Restore from production usually happens overnight. After that I scheduled another job which restores my data and creates stored procs from scripts.
March 10, 2009 at 7:04 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply