Solved

SQL 2005 replcation job for subscriber has been deleted on publication server

Posted on 2009-04-05
4
212 Views
Last Modified: 2012-05-06
Hi,

Sorry if my terminology is rough. SQL replication is not my expertise.

I have a site lets call it SITE 1 and it has a merge publication db setup on it.

SITE 2 and SITE 3 are the subcriber db's for the merge publication set.

SITE 3's SQL replication job has been deleted from the SITE 1 publication server. This happend about 25 days ago, therefore no merge replication has occured between SITE 3 -> SITE 1 for 25 days.

Replication between SITE 2 -> SITE 1 is working fine.

Is there an easy way I can reconfigue or intialise replcation between SITE 3 -> SITE 1 without causing any data issues with SITE 1 and SITE 2 db's. Essentially I need all SITES to be consistent with with one another again.

Regards Craig
 

0
Comment
Question by:Craig_hannagan
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 24078494
<SITE 3's SQL replication job has been deleted from the SITE 1 publication server>
if you have that time MSDB database backup you can get the job from it (after restored on some test server )
from sysjobs\sysjobsteps tables
---
if not - you need to recreate the replication
see example
SQL Server 2005 - Merge Replication Step by Step Procedure  
http://www.codeproject.com/KB/database/sql2005-replication.aspx 
0
 
LVL 15

Expert Comment

by:MohammedU
ID: 24079069
You can restore the backup Site1 or Site2 to Site3 and reconfigure the replication without initializing the snapshot...
Check the following article...

http://msdn.microsoft.com/en-us/library/ms152488(SQL.90).aspx
0
 

Author Comment

by:Craig_hannagan
ID: 24082684
Hi EugeneZ:

Thanks for your comment. I have all system database backups from all of the sql servers a week before the SQL replication job was deleted (so from about 35 days ago). If I was to restore the msdb db onto a test server and then extract the replicacation job and then import it into the production Publisher server will that then envoke replication including the 25 days of existing data (that has not been replicated to Publisher db) + replication from that point on to the Publisher db? I need to get the existing 25 days of un-replicated data to the publisher and then continue replicating from then on without causing any data consistency issues from the other subcriber which has been replicating data to publisher to this date fine.
 
Hi MohammedU:

Thanks for your comment. If I restore a db from Site 1 or 2 I believe I will lose the 25 days of unreplicated data in the SITE 3 subscriber db which I ultimatley need to get replicated back to the Publisher db.

Sorry if I am not following your drift guys.

Thanks Craig
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24082980
depends how much data was moved: at least you may not reinstall replication:
however, if publication is not to big maybe recreate will take less time to bring it to live
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Software for Local History Museum Library 5 51
how to just get time from a date 6 33
Are triggers slow? 7 12
What is this datetime? 1 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question