Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to recover replication when publisher restored from backup (without rebuilding) Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2014 9:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 2,448, Visits: 404
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
Post #1572747
Posted Tuesday, May 20, 2014 9:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 2,448, Visits: 404
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.
Post #1572753
Posted Monday, June 2, 2014 8:33 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 2,448, Visits: 404
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?
Post #1576608
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse