i restored one replicated database in another replicated db, and now cant delete publishers in EM

i restored one replicated database in another replicated db, and now cant delete publishers in EM

Post by Jyothi Pai [M » Sun, 24 Nov 2002 08:42:32

Hi Stephane,

What version of SQL Server are you using? Did you restore a published
database onto another Published database or a Subscriber database?

If you are using SQL 6.5, have you seen these articles:

INF: SQL Server 6.5 Stored Procedure To Remove Replication From Restored

INF: How to Manually Remove Replication from SQL Server 6.x

If you are using SQL Server 7.0/ 2000, when you restore a backup of a
replicated database to a different server or database other than the one on
which it was created, your replication settings will not be preserved.

For publishing databases and merge subscribing databases, a full restore of
the database and logs is followed by an automatic removal of replication
meta data from the database when the database or server you restore to
differs from the one on which the backup was created. If you attach a
database to a server other than the server from which the database was
detached, and the detached database was enabled for replication, you should
run sp_removedbreplication to remove replication from the database.
Alternatively, you can remove replication from the database prior to
detaching it.

You could refer to the SQL 2K books online topic for more information:

Restoring Backups of Replicated Databases to a Different Server or Database

Jyothi Pai
Microsoft Online Support Engineer

Get Secure! www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.


| Subject: i restored one replicated database in another replicated db, and
now cant delete publishers in EM
| Date: Thu, 21 Nov 2002 14:50:27 -0500
| Lines: 7423
| Organization: crea
| MIME-Version: 1.0
| Content-Type: multipart/mixed;
|       boundary="----=_NextPart_000_000F_01C2916D.549021F0"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106

| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host:
| Path: cpmsftngxa08!tkmsftngp01!tkmsftngp08
| Xref: cpmsftngxa08 microsoft.public.sqlserver.replication:34650
| X-Tomcat-NG: microsoft.public.sqlserver.replication
| i restored one replicated database in another replicated db, and now cant
delete publishers in enterprise manager.
| error attached
| how can i delete this publication manually, and what is the best approach
to restore a rep database into another rep database.
| thanks
| stephane


1. Problem restoring replicated database to publisher

Hi all, please excuse the longish post but here's the problem:

Users want a (warm) standby server in the event of production failure,
configured two servers running Win2k server SP1 and SQL Server 7.0 SP2 as,

Server A - Publisher,  Database A (all tables) with immediate updating
subscription enabled to,
Server B - Subscriber,- Pull subscription to Database A.

Works ok, replicating happily, try a failover situation where we:

1.  Disconnect Server A (Publisher) to simulate failure
2.  Cancel Subscription on Server B
3.  User connect to Server B, Database A and continue work.
4.  Backup Server B, Database A and copy to Server A
4.  Bring Server A back online, and that's where the problems start

After restoring the database from server B to A, the Users defined in the
database "disappear" from EM GUI ???

But more importantly, we cannot re-eneable replication on the Database A
again, get the following error

Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'msrepl_synctran_ts'
in table 'AHDQ.act_log' is specified more than once.

Only happens after restoring from Server B, re-enabling replication using
the original database works fine.  The column exists in all tables on both
servers as it is the timestamp column so there is no physical difference but
why does it try to re-add the column in one case and not the other ??????

Any help or ideas greatly appreciated

Colin Taylor

2. Is an additional index needed if I have a double primary key?

3. Replicating DB from a target replicated DB

4. Background processes on NT

5. Replicate, flag archived and delete from publisher - Urgent

6. More on "User Connection" problem

7. Moving a Access replicated database to a MSDE replicated database

8. Where to get the books for older version?

9. Replicated transactions appearing in non-replicated DB!

10. Replicate a table from one db to another db on same server

11. replicate complete DB vs backup/restore

12. Restoring replicated db to new server