SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to recover replication when publisher restored from backup (without rebuilding)


How to recover replication when publisher restored from backup (without rebuilding)

Author
Message
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 425
Hi, I hope someone can point me in the right direction here.

We have a larger (1T and growing) application database (mostly xml data store) that we replicate for reporting and analysis purposes. Due to the significantly different needs of the reporting and analysis, we apply several layers of transformation to the data on its way to the subscriber (convert UTF-8 varchar(max) to UTF-16 xml (don't ask), add some persisted calculated columns to extract data from xml, add indexing, etc).

As a result of all of these modifications to the data it can take an extended period of time (about a week the last time we did it) to fully rebuild replication.

We have occasional high risk situations (application version upgrades) where our standard practice has always been to take the system to single user mode, back up the database, perform the upgrade and roll back to the backup if necessary. Unfortunately in my searches I have been unable to find any clear way to restore both the publisher and subscriber and get replication back up and running at a point in time.

Has anyone had any experience with restoring replication (both the publisher and subscriber) and getting everything back in sync? If so could you provide some feedback or links to documents discussing some how and why? so that we can build out an internal process?

any help that anyone could provide would be much appreciated.

Thanks,
Kevin
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 425
I forgot to mention, I know this is referring to SQL Server 2005, but we are running our publisher as 2008 R2 (enterprise/processor) and subscriber/distributor as 2008 (standard/CAL) and a second subscriber running 2008 (standard/processor). I'm guessing the same rules/processes/gotchas apply to most post 2005 versions of sql server.
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 425
I found this article which appears will provide what I need:

http://msdn.microsoft.com/en-us/library/ms152560(v=sql.105).aspx

has anyone had any experience with this process and know of anything I should watch out for?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search