Re: Restore Replicated Database

Paul:

Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.

I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?

Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.

Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.

Have I missed anything?

Thanks!

"Paul Ibison" wrote:

> As long as you change references to the server (eg @publisher, @subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
>
> "A. Robinson" <ARobinson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@xxxxxxxxxxxxxxxx
> > I'm going to create a job that will run after the restore has completed to
> > recreate the publicaion and subscriptions.
> >
> > Will the fact that the distribution database is "different" than the
> > database in production, will recreating the publication on the other
> > server
> > still work? The articles and everything will still be thae same.
> >
> > "Paul Ibison" wrote:
> >
> >> This is correct - the different server names preclude a successful
> >> restore
> >> of the replication settings. The easiest solution is to use the
> >> replication
> >> script generated on the origional server and search and replace on the
> >> servername. You might also have to change the job owners if they have not
> >> been set to sa.
> >> Rgds,
> >> Paul Ibison SQL Server MVP, www.replicationanswers.com
> >> (recommended sql server 2000 replication book:
> >> http://www.nwsu.com/0974973602p.html)
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • RE: Load Balanced Web Synchronization Against a Republisher Topology &
    ... publication metadata, in the distribution db's, we'ere identical... ... IIS server has a dedicated republisher pointing to a central server, ... We setup the client inline with the subscription ... properties necessary to use Web Synchronization. ...
    (microsoft.public.sqlserver.replication)
  • Re: process could not execute sp_repldone/sp_replcounters
    ... if you script out your publication first it will be easier for you to ... the publisher only you can orphan them. ... I can normally right click on the subscription and then delete it. ...
    (microsoft.public.sqlserver.replication)
  • Re: One table for both subscription and publication
    ... I have problem with configuring the middle publisher. ... I create its subscription, it doesn't then allow me to create a publication ... based on a table which is receiving data from master server. ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent not Visible
    ... If you have a publication on a database, and you script it out, modify the ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: urgent problem
    ... first clean up your subscription database. ... Looking for a book on SQL Server replication? ... Maybe I did not remove the publication ...
    (microsoft.public.sqlserver.replication)
Loading