Add a new site into a replicating SQL server system

Posted on 2011-03-02
Medium Priority
Last Modified: 2012-05-11
Hi Experts

I have a SQL server 2005 distribution server/publisher at my head office and 4 sites that subscribe to it, all merge subscriptions.

One of these sites has had a hard disc failure and no good backup. Not a huge problem as effectively I have a backup at the head office and the other 3 sites.

Can I restore a backup at the faulty site from a working site and just subscribe to my publisher again without causing any problems?

Question by:alexking
  • 2
  • 2
LVL 29

Expert Comment

by:Paul Jackson
ID: 35019314
Yes that will work, best to take the backup from the publisher.
LVL 12

Expert Comment

by:Norman Maina
ID: 35019322
It will work -when synching,sql server will create unique identifiers for the newly restored. db.

Author Comment

ID: 35019388
So if I delete the now non-functioning subscription from my publisher and create a new one I should be all fine?
Also because the 2nd disc in the server at the failed site has crashed with the database on it, when I run management studio, I can see the database listed but I cannot access it or do anything with it. Should I detach this defunct database and then just retore the backup?

Thanks for your speedy help!
LVL 29

Accepted Solution

Paul Jackson earned 2000 total points
ID: 35019441
Yes delete the subscription, detach the old database, restore from backup. Depending on your security model i.e. sql or windows, you may have to setup security for the database again.
Is the server in a different domain to the server where the backup is coming from? and if you use windows authentication the you will need to delete the logins from the database and add back in again.

Author Closing Comment

ID: 35122895
Sorry for the delay closing this, thanks for the re-assurance and it worked a charm!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 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