Back up /restore of a replication database.

  • I have a server-client setup where the server system is WindowServer2008 (server1) and  client system is WIn7 system(Client1)

    Server1 has SQLServer2008 and SQLExpress

    Client1 has SQLexpress.

    Server1 has MainDB with table1 under SQLServer2008 instance  - this is also the publisher and distributor.

    It also has ClientDB under SQLExpres instance - subscriber 1

    Client1 has ClientDB under SQLExpress instance - subscriber 2

    Data is replicated from MainDB to ClientDB on both systems.The restore procedure we use is
    1. Stop SQL agent, restore MainDB, distribution and msdb. I could no find a way to restore master.
    2. remove all publication/subscriptions and recreate them (this is done as we see some issues with replication after restore).

    These steps work as long as the server1(publisher, distributer) has not failed, Now is a situation where server has failed,

    Qs: I am facing a situation where the Server1 system has failed and need to be restored.

    I installed my server 1 with my application and  restored MainDB, msdb and, distribution from backup. After this,  I see publication/subscriptions for both server1 and client1 under replication folder in SSMS.

    I want to remove all publication/subscriptions and recreate them  so that there are no issues with data replication.

    now I am unable to remove publication/subscription for client1. I am getting error 'client1/dbinstance' is not a subscriber for 'server1'

    I have looked at the msdn link for restore and at the end it states to remove and recreate publication/subscriptions.

     How do I clean up and restore my systems from backup?

  • Hi,

    Not sure why you restoring distribution db when later you want to drop everything to recreate all replication setup? You can create new distribution db and configure it from scratch.
    As for dropping publications etc. did you try T-SQL?
    exec sp_dropdistributor @no_checks=1,@ ignore_distributor=1
    This will cleanup everything on publisher.

    Regards,
    B

  • I want to do cleanup because I am seeing SQL errors after restore and data replication is failing. All the suggestions lead me to do a clean up of publications and subscriptions and recreate them. I do no twnat to remove distribution and publication databases. Just the publication/subscriptions.

  • Still u can run this on Publisher:
    exec sp_dropdistributor @no_checks=1,@ ignore_distributor=1 
    It will cleanup publisher. I did not say to remove publication database just distribution db (as the data inside that db will be useless anyway after u drop publications and subscriptions). However since u restored distribution db there will be still some information about old replication.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply