Restore Replicated Database

I seem to have run into a problem within an environment I have set up with
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
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)
|||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)
>
>
|||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@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> 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:
|||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@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:

> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>