When I try to drop the XXX database SQLServer returns me this error message:
Server: Msg 3724, Level 16, State 3, Line 1Cannot drop the database XXX because it is being used for replication.
I've also tried to execute the following code but nothing has changed.
exec sp_removedbreplication 'XXX'goexec sp_dboption 'XXX','published',falsegoexec sp_dboption 'XXX','merge publish',falsego
The XXX DB is a restore of "Distribution" database of a Production Server that is a publication server!
Someone can help me?
Thanks for all suggestions!!!
Please, run sp_helpreplicationdb to check with databases are marked for replication.
Distribution is for replication but should not be marked for replication, so you should be able to restore it at any time.
I want to drop XXX Db that contains a restore of "distribution" database of a production server. I've tried to use sp_helpreplicationdb but I'm not be able to drop XXX DB.
Can you give me some more advices?
what type of replication?
What I've had to run to make sure my scripts are starting clean for merge replication are as follows:
'Drop any subscriptionsexec sp_dropmergesubscription @publication = N'<publication>''
'Drop the publicationexec sp_dropmergepublication @publication = N'<publication>'
'Set the DB to not be for replicationexe sp_replicationdboption @dbname= N'<db>' @optname=N'merge publish' @value = 'FALSE'
If you're using trans rep I believe the commands are similar.
Also if you go into EM and right-click the publication you can choose 'generate script' which will give you the option to see the script for creating or droping the publications.
although it's not recommended, sometimes you 'll have to update the REPLINFO column in SYSOBJECTS.
You can restore another database backup, that don`t have replications configured, over the database that you want to delete(with the "force restore over existing database" option marked). Doing this you will overwrite the sysobjects lines that saves the replication parameters and the SQL Server will alow you to delete the database.
I hope It`s easy to understand..
take the datbase offline and then drop it.