restore the replication system -- sql server replication

Wen Chang
8/7/2004 2:47:01 PM

Paul.

1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.

I will attache back the master and msdb and see if it works.

Thanks for the help.

Wen

[quoted text, click to view]

Paul Ibison
8/7/2004 7:35:45 PM

Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison

Paul Ibison
8/8/2004 9:16:46 AM

Wen,

there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.

As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.

You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.

HTH,

Paul Ibison

Hilary Cotter
8/9/2004 8:11:51 AM

Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.

The sequence to get this to work is

1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.

In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.

The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Hilary Cotter
8/9/2004 12:53:10 PM

BOL lies.

Try to restore a tlog with the keep_replication switch.

I have reported this doc bug to Microsoft.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Paul Ibison
8/9/2004 5:03:24 PM

Hilary,

from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.

However in BOL
(mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:

- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.

Are these just alternative methods?

Regards,

Paul Ibison

Paul Ibison
8/10/2004 9:12:43 AM

Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : restore the replication system