possible to re-add replication after manually removing it? -- sql server replication

Rich
8/9/2006 8:20:02 AM

Hi Paul,

Thank you for your reply (I'm reading Hillary's book and in the
acknowledgements he mentions that you would be quite active in providing
assistance in this NG :). Well, I did wonder about the Generate Sql Script
option from the dropdown Menu of the publication.

Just to get it straight, if I generate/save the script, can I run it in QA
and it will restore the replication? I guess I could try this out on my test
Repl DB and find out. And just to get ahead of myself, I looked at the sql
script of my test Repl DB which the test Repl DB also has a push
subscription. I did not see any sql that appeared to address the push
subscription. Is it there a reference in there that I missed or is this
where manual intervention is required?

Thanks,
Rich

[quoted text, click to view]

Rich
8/9/2006 8:55:03 AM

Well, here are the results of my experiment.

I generated the Sql Script from the right-click dropdown menu. Then I
dropped replication on my test Repl DB. I detached the DB, deleted the log
file, re-attached the DB, and then ran the script that I generated in QA. I
got the following error messages:

----------------------------------------------------------------------------------------
Server: Msg 14005, Level 16, State 1, Procedure sp_MSdrop_publication, Line 48
Could not drop publication. A subscription exists to it.
Server: Msg 15001, Level 11, State 1, Procedure sp_addpublication_snapshot,
Line 117
Object 'testWebSubscriber2' does not exist or is not a valid object for this
operation.
Server: Msg 14027, Level 11, State 1, Procedure sp_addarticle, Line 480
testWebSubscriber2 does not exist in the current database.
Server: Msg 20026, Level 11, State 1, Procedure sp_addsubscription, Line 137
The publication 'testWebSubscriber2' does not exist.
----------------------------------------------------------------------------

Any advice on what I need to do about this would be greatly appreciated.

Thanks,
Rich

[quoted text, click to view]

Paul Ibison
8/9/2006 9:25:46 AM

Rich,
the replication monitor issue relates to the fact that the replication
monitor gets its info from tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table.
To recreate a publication, provided the database name and server name is the
same, the simplest way is to script out the original publication and
subscriptions then run the script in the 'new' database. You could also set
it all up programatically, but the fact that the EM will script out the
publication for you makes this an easier option.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Rich
8/9/2006 9:34:01 AM

I decided to start over. Good learning process. I was trying to drop the
distributor, more errors. Then I discovered the Management console/job. I
deleted everything there (It is just a test server - I can get away with this
- here is where I make all the mistakes). I was now able to drop the
distributor. So I will start everything from scratch. Create whole new
distributor DB - look at the Management/Jobs folder to see what was added,
add new publication - look at the Jobs, add new subscription - look at the
jobs, then remove replication from the test Repl DB and see if I can restore
it.

Rich

Rich
8/9/2006 10:16:02 AM

Thank you as always for your reply. As for truncating the log, I have tried
the simple recovery method, but I must confess that I don't know how to set a
checkpoint such that the log file can be truncated from that point forward
(is that what the checkpoint does?). Ideally, I just need to shrink the log
file to its default size when it is brand new (512K), or at least less than
100 megs. It is like 12 gigs right now.

As for the replication exercise, I have been doing everything Replication
related via QA using the SPs. Maybe I might be better off using EM while I
am just starting out with Replication. Either way, I dropped everything
-distributor, etc, and am starting over on the test server. But I would be
grateful if you could share how to set the checkpoint on the Production
system so that I can (safely) shrink the log file.

Thanks,
Rich

[quoted text, click to view]

Paul Ibison
8/9/2006 4:44:19 PM

Hi Rich - I was secretly hoping that someone would read my name in Hilary's
book - now I can retire happy ;)
Scripting out replication is slightly different in different places - the
most options are seen when you right-click the publications folder in the
published database. Even so, you should see sp_addsubscription in all nodes
of EM - is it definitely not there?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul Ibison
8/9/2006 5:27:41 PM

Rich - I need to understand what it is you have done here. The messages
you've posted presumably came when you 'dropped replication'? If this is the
case, how did you remove the publication? If you run it from EM, the
subscriber will get dropped first, which doesn't seem to have happened in
your case. Also, I don't follow why you deleted the log. If you want it
truncated, normally you'd set the database in simple recovery mode and issue
a checkpoint.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul Ibison
8/9/2006 10:28:15 PM

Rich,
setting the simple recovery mode is what used to be called 'truncate on
checkpoint'. You will probably find that the space occupied by the log
shrinks considerably on setting the simple recovery mode. As an extra
precaution, you can run 'CHECKPOINT' in a QA window. After that you'll need
to shrink the log file - not the database. So DBCC SHRINKFILE is what is
needed (TRUNCATEONLY). This is available from EM also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : possible to re-add replication after manually removing it?