November 9, 2010 at 9:34 am
We have three database env, Prod, Dev,Test. Each db servers has four databases(for sake of ex: A, B,C,D) with replication where A is publisher and B,C,D are subscribers. We aren't using Snapshot replication but table replication (very new to this so typing whatever I have heard so it can help whoever is reading this). I need to completely refresh Dev/test evn with data ONLY from Prod. And I need to retain the original users on each db servers/ea. databases. My first thought was to do a prod db backup and restore on dev/test. However, wouldn't this break replication and also overwite the database users? What is the best route to achieve what I am needing, such that replication doesn't break on dev/test and all original users are also retained. Any help would be apprecaited. All three evn are using SQL serverr 2008 64 bit Standard.
Thank you,
November 9, 2010 at 10:51 am
You can set one of the options for replication to not include schema changes.
But even if you do, what happens when someone adds or changes a column in one of your replicated tables? Do you not include the new column? Do you force data truncation if the column was expanded?
I guess I don't understand why you'd replicated data from one to the other for those, without replicating schema changes.
- 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
November 9, 2010 at 11:16 am
rohit79 (11/9/2010)
I need to completely refresh Dev/test evn with data ONLY from Prod.And I need to retain the original users on each db servers/ea. databases.
These two items I understand to be the crux of your issue. First, there's three types of replication. Merge, Transaction, and Snapshot. If I understand you correctly, you're doing transaction.
Now, with transaction replication on *every* table, you should only have production data there anyway. Is this not the case? Can you confirm these tables are actually subscribed properly?
As to your second issue, there are ways to script out all the users/permissions in a database. To allow you to disconnect replication from prod (which I would do anyway, as I'd need to make schema modifications and the like at the dev level) you could script out the logins, restore the database, wipe the logins (and thus their permissions) that you brought down, and then rebuild them from your script. Obviously test your script in a database copy before blowing away your real dev environment in case you needed more information.
Those scripts can be found online with some searching, or perhaps someone will be kind enough to post a link. I've forgotten where I found them last.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply