Drop and Re-create Subscription on a server(P2P Replication)

Posted on 2011-04-25
Last Modified: 2012-06-21
I have a peer to peer transitional replication running on two servers. All of a sudden the subscriber on one of the servers went down with the following error.
How do I drop and recreate a subscription in MSSQL 2008?
Question by:isaackhazi
    LVL 13

    Expert Comment

    To Drop Subscription on a server(P2P Replication) refer the link:

    To Re-create Subscription on a server(P2P Replication) refer the link:
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Using Context menu delete the subscription and create using wizard.
    LVL 8

    Author Comment

    I cannot do that as it is a P2P trans replication.

    I deleted it using the context menu but Im unable to add the subscription back...

    So then I tried to add it thru a SP ..

    use [PRTR_recruit_live]
    exec sp_addsubscription @publication = N'PublicationPRTR', @subscriber = N'prtrescrmserver', @destination_db = N'PRTR_recruit_live', @sync_type = N'initialize from lsn'

    with the above I get an error saying @subscriptionlsn cannot be NULL...

    Any ideas...

    I seriously dont want to re-do the entire replication again. as the other server is at an off site location about 200 kms from here and the database size is 40 GB
    LVL 8

    Accepted Solution

    I figured it out.

    set @sync_type='replication support only' and @status = N'active'
    use [PRTR_recruit_live]
    exec sp_addsubscription @publication = N'PublicationPRTR', @subscriber = N'prtrescrmserver', @destination_db = N'PRTR_recruit_live',@sync_type='replication support only', @status = N'active'

    Open in new window

    LVL 67

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now