Restore without Replication

Is there a way to drop replication on a restore. Or, is there a way to
backup without saving the replication information? I tried setting
KEEP_REPLICATION for false but if its is the original server the
replication still shows up. I also tried running
sp_restoredbreplication on the database after I restored it. However
the query analyzer said it completed successfully but I still show a
publication. I want to restore an not have the publication show up so
we can rebuild the replication and in 2 cases not replicate anymore.
If you have it in SQLDMO would be grand but right now anything would
help.
Thanks for your help.
gslim
Its a little tricky. Some of the replication metadata remains. I find it
necessary to go in there and delete it manually.
BTW Are we talking merge or transactional?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Shane Lim" <gslim@.blizzardice.com> wrote in message
news:iq1v71d1un7ujjnr0d76n9gr501ar680uh@.4ax.com...
> Is there a way to drop replication on a restore. Or, is there a way to
> backup without saving the replication information? I tried setting
> KEEP_REPLICATION for false but if its is the original server the
> replication still shows up. I also tried running
> sp_restoredbreplication on the database after I restored it. However
> the query analyzer said it completed successfully but I still show a
> publication. I want to restore an not have the publication show up so
> we can rebuild the replication and in 2 cases not replicate anymore.
> If you have it in SQLDMO would be grand but right now anything would
> help.
> Thanks for your help.
> gslim
>
|||We are talking about merge replication.
I was running sp_restoredbreplication but I am going to try also
running sp_removedbreplication (suggested by Paul Ibison in another
thread I found.) after that and try again.
gslim
On Mon, 9 May 2005 12:37:06 -0400, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>Its a little tricky. Some of the replication metadata remains. I find it
>necessary to go in there and delete it manually.
>BTW Are we talking merge or transactional?
|||Ok so currently I run both of the stored procedure
sp_removedbreplication
then
sp_restoredbreplication
and I can create the publication fine.
However when I create the subscription I still get the error on the
subscription of
Invalid object name 'dbo.sysmergearticles'
Any ideas?
On Tue, 10 May 2005 08:47:45 -0600, Shane Lim <gslim@.blizzardice.com>
wrote:
[vbcol=seagreen]
>We are talking about merge replication.
>I was running sp_restoredbreplication but I am going to try also
>running sp_removedbreplication (suggested by Paul Ibison in another
>thread I found.) after that and try again.
>gslim
>On Mon, 9 May 2005 12:37:06 -0400, "Hilary Cotter"
><hilary.cotter@.gmail.com> wrote:

restore to new hardware

We wil be upgrading all of our sql servers to new hardware and from Windows
2000 to 2003. The servers are in a merge replication topology.
We hope to keep the server names the same.
It would be an extreme hardship to drop the publications and recreate them.
Given these parameters, what is the best approach?
Do one last sync. Take the publisher offline, back up all databases to tape,
restore on the new server. Reboot. It should come up automagically.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
news:AA8B3EBA-C4C7-468B-AB23-27CFC725DB48@.microsoft.com...
> We wil be upgrading all of our sql servers to new hardware and from
> Windows
> 2000 to 2003. The servers are in a merge replication topology.
> We hope to keep the server names the same.
> It would be an extreme hardship to drop the publications and recreate
> them.
> Given these parameters, what is the best approach?
>
|||Just to be clear...
When you take the publisher offline, do you mean stop all agents?
When restoring to new server, here is the plan:
Install SQLServer with same service packs and hotfixes as default instance
on server with the same name as original.
Start SQLServer in single user mode.
Restore master and msdb
Start SQLServer in normal mode.
Restore Distribution, model, tempdb and user databases.
Is this correct? Am I missing anything?
"Hilary Cotter" wrote:

> Do one last sync. Take the publisher offline, back up all databases to tape,
> restore on the new server. Reboot. It should come up automagically.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
> news:AA8B3EBA-C4C7-468B-AB23-27CFC725DB48@.microsoft.com...
>
>
|||Forget what I said about restoring the tempdb...brain fart.
"CHeineken" wrote:
[vbcol=seagreen]
> Just to be clear...
> When you take the publisher offline, do you mean stop all agents?
> When restoring to new server, here is the plan:
> Install SQLServer with same service packs and hotfixes as default instance
> on server with the same name as original.
> Start SQLServer in single user mode.
> Restore master and msdb
> Start SQLServer in normal mode.
> Restore Distribution, model, tempdb and user databases.
> Is this correct? Am I missing anything?
> "Hilary Cotter" wrote:
|||Yes, run the agents one last time until they stop or report no replicated
data. Then follow your steps and in the final restore of the user database
use the keep_replication switch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
news:81899ABF-321B-42E2-9370-5C7143B02E63@.microsoft.com...[vbcol=seagreen]
> Just to be clear...
> When you take the publisher offline, do you mean stop all agents?
> When restoring to new server, here is the plan:
> Install SQLServer with same service packs and hotfixes as default instance
> on server with the same name as original.
> Start SQLServer in single user mode.
> Restore master and msdb
> Start SQLServer in normal mode.
> Restore Distribution, model, tempdb and user databases.
> Is this correct? Am I missing anything?
> "Hilary Cotter" wrote:

restore the replication system

Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:

> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison

Restore published database to test server without replication?

What is the correct way to restore a backup of a published database onto a
different server with no replication? Here's my situation. The production
db (Sql 2k sp3a) is published using transactional replication and a remote
distributor. I restored a full backup of this database onto a test server
without replication. But I can't truncate the t-log because there are
un-replicated transactions, which can be seen with DBCC OPENTRAN. I tried to
execute sp_repldone NULL, NULL, 0, 0, 1 but get the message that the db is
not published. Sp_removedbreplication executes, but I still have the same
problems. I finally enabled the test server/db as a publisher, ran
sp_repldone, and then removed replication.
Is there a better way to do this?
Donna,
you seem to have done all the correct things. I'm not too sure that it is a
better way, but I have got round this type of issue by detaching the
database, renaming the log then reattaching, and a new (empty) log has been
created. This is obviously only any good if you can afford to lose the
transaction log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Restore publication db

Hello All -
When trying to restore the publication database, I am losing the
publication! ( Running merge replication, SQL Server 2000, push
subscriptions).
I am using the RESTORE command as follows:
RESTORE DATABASE CDB FROM DISK = 'c:\temp\somefile.bu'
WITH KEEP_REPLICATION
I had a 'successful' return from running the above command.
When I open EM \ DB \ Replication, my publication is gone. The merge
agents are there but if I try to reinit, it tells me that the publication
does not exist.
What am I doing wrong?
Thanks,
bruce
Something is strange here. This should work. Does the server you are doing
your restore on have replication installed on it?
What happens if you enable the database you are going to restore to for
merge replication and then do the restore with the keep_replication switch?
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:JUREc.10114$oW6.1420194@.twister.nyc.rr.com...
> Hello All -
> When trying to restore the publication database, I am losing the
> publication! ( Running merge replication, SQL Server 2000, push
> subscriptions).
> I am using the RESTORE command as follows:
> RESTORE DATABASE CDB FROM DISK = 'c:\temp\somefile.bu'
> WITH KEEP_REPLICATION
> I had a 'successful' return from running the above command.
> When I open EM \ DB \ Replication, my publication is gone. The merge
> agents are there but if I try to reinit, it tells me that the publication
> does not exist.
> What am I doing wrong?
> Thanks,
> bruce
>
>
>
|||Bruce,
are you restoring to a different server. If the servername is different,
this is normal behaviour in my experience as the servername is entered into
several metadata tables eg In sysmerge articles there is a publisherid. This
ID needs to have a corresponding record in sysmergepublications. However, in
sysmergepublications, the 'publisher' column needs to match the servername,
which it doesn't in this case as you have restored to another servername. In
this case you need to script out the publications on the initial server,
modify the scripts and then reapply them on the second server.
HTH,
Paul Ibison
|||Hillary -
I have merge replication working on the publication database ( publication &
distribution on same machine). I then stop all agents to get exclusive
access to the db. I run the restore command with the KEEP_REPLICATION flag.
When I go to reinit all subscribers, the publication is gone.
I'm not sure of your question

> What happens if you enable the database you are going to restore to for
> merge replication and then do the restore with the keep_replication
switch?
I am backing up the publication database and then restore the publication
database.
Regards,
bruce
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uTGIZ$1XEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Something is strange here. This should work. Does the server you are doing
> your restore on have replication installed on it?
>
> What happens if you enable the database you are going to restore to for
> merge replication and then do the restore with the keep_replication
switch?[vbcol=seagreen]
> "rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
> news:JUREc.10114$oW6.1420194@.twister.nyc.rr.com...
publication
>
|||Paul & Hillary-
Thanks for the replies.
That's it! I am restoring a backup from the production machine to my
developement machine.
I guess to do this I should use the scripting repl. wizard to create the
repl setting on the development machine. Oh the tangled web we weave...
Regards,
bruce
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eC$UYE2XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> are you restoring to a different server. If the servername is different,
> this is normal behaviour in my experience as the servername is entered
into
> several metadata tables eg In sysmerge articles there is a publisherid.
This
> ID needs to have a corresponding record in sysmergepublications. However,
in
> sysmergepublications, the 'publisher' column needs to match the
servername,
> which it doesn't in this case as you have restored to another servername.
In
> this case you need to script out the publications on the initial server,
> modify the scripts and then reapply them on the second server.
> HTH,
> Paul Ibison
>
|||again this should restore your merge publications intact.
To go over the steps you have a production machine and a test machine.
replication is installed on both of them. You have two databases which are
enabled for merge replication. You back up the existing production merge
replication database and restored it to a database which is enabled for
merge replication in the test environment.
Your merge replication publications (but not the subscriptions or agent
info) is restored into the test database.
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:PMTEc.10124$oW6.1440077@.twister.nyc.rr.com... [vbcol=seagreen]
> Paul & Hillary-
> Thanks for the replies.
> That's it! I am restoring a backup from the production machine to my
> developement machine.
> I guess to do this I should use the scripting repl. wizard to create the
> repl setting on the development machine. Oh the tangled web we weave...
> Regards,
> bruce
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eC$UYE2XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> into
> This
However,[vbcol=seagreen]
> in
> servername,
servername.
> In
>
|||Yes that is the scenario.
It seems that the BACKUP command should have a NOT_FOR_REPLICATION flag or
something similar. In order to skip the replication backup.
Thanks again for the replies.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eTCpL02XEHA.1000@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> again this should restore your merge publications intact.
> To go over the steps you have a production machine and a test machine.
> replication is installed on both of them. You have two databases which are
> enabled for merge replication. You back up the existing production merge
> replication database and restored it to a database which is enabled for
> merge replication in the test environment.
> Your merge replication publications (but not the subscriptions or agent
> info) is restored into the test database.
> "rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
> news:PMTEc.10124$oW6.1440077@.twister.nyc.rr.com...
weave...[vbcol=seagreen]
different,[vbcol=seagreen]
publisherid.[vbcol=seagreen]
> However,
> servername.
server,
>

Restore of Publication Database caused error

Dear All,
We are currently using Transactional Replication for a Warm Standby.
We needed to perform a restore of out Publication Database last night and
now we are getting the error :-
DATE/TIME:07/04/2005 09:50:29
DESCRIPTION:Error: 14151, Severity: 18, State: 1
Replication-Replication Transaction-Log Reader Subsystem: agent
INVEST1-ParisProduction-12 failed. The process could not execute
'sp_repldone/sp_replcounters' on 'INVEST1'.
COMMENT:One of the replications has failed, sort it out
JOB RUN:(None)
My normal fix for this would be to re-synchronise, but as the publication is
rather big I was wondering if there was a better way of doing it.
Thanks
Peter
try sp_replrestart or sp_repldone (EXEC sp_repldone @.xactid = NULL,
@.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Thanks Guys,
Peter
"Peter Nolan" wrote:

> Dear All,
> We are currently using Transactional Replication for a Warm Standby.
> We needed to perform a restore of out Publication Database last night and
> now we are getting the error :-
> DATE/TIME:07/04/2005 09:50:29
> DESCRIPTION:Error: 14151, Severity: 18, State: 1
> Replication-Replication Transaction-Log Reader Subsystem: agent
> INVEST1-ParisProduction-12 failed. The process could not execute
> 'sp_repldone/sp_replcounters' on 'INVEST1'.
> COMMENT:One of the replications has failed, sort it out
> JOB RUN:(None)
> My normal fix for this would be to re-synchronise, but as the publication is
> rather big I was wondering if there was a better way of doing it.
> Thanks
> Peter