June 23, 2006 at 3:11 pm
I need to make some big time changes to the publisher. So I want to disable replication, make all the changes, back up the db and restore it on the reporting server and setup replication between the PROD and REPORTing db's. How do we resolve the IDENTITY column issues? All the (few) articles I have see on google talk about creating blank tables and set the IDENTITY NOT FOR REPLICATION option but there is not a single article that talks about setting up REP from a restored copy.
Anyone has any ideas?
Thanks,
Dinakar
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
June 25, 2006 at 1:35 pm
you can restore PUB DB on Subscriber DB & do replication with "NO SYNC". Depends what you are going to add or remove from PUB DB, look for sp_repladdcolumn & sp_repldropcolumn BOL. i hope this will help
June 26, 2006 at 12:02 pm
we are changing the datatypes of columns from int to bigint etc. so repladd/repldrop will not work.
thanks
Dinakar
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
June 27, 2006 at 7:07 am
Dinakar
Surely it will work if you restore your database at the subscriber, set up the publication and then start synchronising? If identity insert is on, then the values in the identity column will be copied into the subscription database, and if it isn't, the next row will be populated by the next value in the identity sequence anyway. That's assuming you aren't filtering the data you publish...
John
June 27, 2006 at 9:53 am
Thanks John.
I was finally able to get it set up and running. I am gonna make a document and put it on my blog somewhere one of these days. I realized we dont need to make any additional changes for IDENTITY columns.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply