November 23, 2011 at 7:32 am
I am testing to move DB to a new disk formation but while a replication on the DB is working, it doesn't allow me to do it.
Because of that reason first I want to try a few test to do this on my computer instead of live DB.
I thought I set the same settings as on Live DB but I get an error on the second day that it says
"The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized."
Then I try to do what it says but this time I get
"Violation of PRIMARY KEY constraint 'PK_table'. Cannot insert duplicate key in object 'table'.
As I understand that its reason is I have chosen at the snapshot "keep the existing table unchanged"
Actually I don't want it to drop tables or delete all datas at the subscription because it may take too much time.
If I am sure about both 2 DBs are sync, is there an option for snapshot without deleting data at subscriber?
Or is there an easy method to move DB without removing replication?
November 23, 2011 at 8:02 am
For snapshot replication, the usual answer is to empty the tables before inserting new data into them. That's kind of the point of snapshot replication.
When I need to create a copy of a database, I usually just do a copy-only backup, then restore it in the new location.
- 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 23, 2011 at 8:38 am
Sorry, I didnt mention the type of rep. I am using Merge replication, the snapshot that I mention is at the beginning of it to sync data.
If is there an option as skipping the snapshot that maybe useful for me too.
November 23, 2011 at 9:01 am
I'm not sure how you'd do replication without a snapshot being used somewhere along the way.
- 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 25, 2011 at 8:32 am
I found an option to my question.
When you are defining subscriber there is an option.
Initialize subscription. Instead of choosing Yes to it as default you may choose:
No, Subscriber already has the schema and data.
And I found another forum for my solution to move replicated database:
http://www.sqlservercentral.com/articles/Replication/movingreplicateddatabases/2259/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply