deleting restored dB that is read-only

I restored a dB and set it as read-only. The dB is being
used for replication, so in my Replication Monitor in EP,
all the publications are listed numerous times. I cannot
detach the dB because it is being used for replication.
I cannot use sp_removedbreplication because it is read-
only. So the question is, how do I remove the dB? How
do I change the read-only flag off?
Larry,
this should remove the database for you (just need to replace xxx with your
database name).
alter database xxx set read_write with rollback immediate
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
Regards,
Paul Ibison
|||Paul,
I ran the commands you suggested and received the
following errors...
Server: Msg 5063, Level 16, State 1, Line 1
Database 'StoreMain-lpr10' is in warm standby. A warm-
standby database is read-only.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 3906, Level 16, State 1, Procedure
sp_dropsubscription, Line 441
Could not run BEGIN TRANSACTION in database 'StoreMain-
lpr10' because the database is read-only.
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'StoreMain-lpr10' because it is
being used for replication.
HELP!!!
Larry...
|||Larry,
this database is in RO mode because it has been restored without recovery.
The following script should be run before my original one:
alter database StoreMain-lpr10 set single_user with rollback immediate
go
restore database StoreMain-lpr10 with recovery
go
Regards,
Paul Ibison

deleting publications

Hi,
I created a few push subscriptions in continuous mode for testing
merge replication. I stopped the synchronization before I tried to
delete the publication. I got an error stating, can't delete because
the publication is being used for replication. I can't delete the
subscriptions on the subscriber as well. The tables associated with
the publications also can't be deleted. I don't want to use the Wizard
to delete all the publications.
Does anyone know how I can delete the publications?
Thanks!
PS.
PS,
try using sp_dropmergesubscription for each subscription, followed by
sp_dropmergepublication. Please report back the exact error message if there
is one.
When we can get the publication deleted: to ultimately drop the table you
can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
a tablename as a parameter. Alternatively, running sp_removedbreplication
can be used to remove all traces of replication in the subscriber database,
but obviously must only be done if this database is not also configured as a
publisher.
HTH,
Paul Ibison
|||Hi,
I tried what you suggested and I got rid of the subscriptions and
publications.This is what I did:
I stopped synchronization, and deleted the publication entry under
Replication.
I then called sp_MSunmarkreplinfo to delete the table used for
replication.
On the subscriber however,
I had to delete the entries from 'sysmergearticles' and
'sysmergepublications' in order to delete the subscription entry under
'replication->subscriptions' and then call sp_MSunmarkreplinfo' to
delete the table.
Thanks for the help!
PS.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<urzlrrQVEHA.584@.TK2MSFTNGP09.phx.gbl>...
> PS,
> try using sp_dropmergesubscription for each subscription, followed by
> sp_dropmergepublication. Please report back the exact error message if there
> is one.
> When we can get the publication deleted: to ultimately drop the table you
> can use a stored procedure to do this called sp_MSunmarkreplinfo which takes
> a tablename as a parameter. Alternatively, running sp_removedbreplication
> can be used to remove all traces of replication in the subscriber database,
> but obviously must only be done if this database is not also configured as a
> publisher.
> HTH,
> Paul Ibison

Deleting Conflict tables

Hi,
While trying Merge replication for various publications, many conflict
tables were created in the publisher database. Even after deleting the
publications, they haven't gone off and neither can I delete them.
Could someone suggest, how to delete the conflict tables?
Thanks!
PS.
Sometimes the sp_removedbreplication will not remove them. If you ALTER the
tables, then you should be able to just drop them.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Thanks for the response. I tried deleting them through Enterprise
Manager and I got errors, but for some reason, I am able to drop the
conflict tables by dropping them from query analyzer.
PS.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<g3Y0gn6UEHA.1996@.cpmsftngxa10.phx.gbl>...
> Sometimes the sp_removedbreplication will not remove them. If you ALTER the
> tables, then you should be able to just drop them.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
sql

deleting a publication

Hi,
I removed a publication using the enterprise manager, and it has gone from
the publications views, but i can make the db ofline, because it reports it
is still published.
What can i do to tidy this up?
Thanks
Duncan
Duncan,
try
USE master
EXEC sp_dboption 'your db name', 'published', 'FALSE'
EXEC sp_dboption 'your db name', 'merge publish', 'FALSE'
HTH,
Paul Ibison