I wanted to drop and restore a database which I restored couple days ago in a test environment. The same db in production is the publisher and there are objects in transactional replication. I was asked to restore the new backup. However drop database returned the error message in the subject line and did not drop the db. Little bit of searching and I found out that the stored procedure can be used to remove the replication prior to dropping the database in test environment. So I connected to my test server and executed following statements in the order.
1. USE MASTER EXEC dbo.sp_removedbreplication @dbname = 'MyDatabase'
2. USE MASTER DROP DATABASE MyDatabase
It worked and I was able to drop and restore the latest backup to the test environment.
Do not run this in production since it will remove the replication from your production publisher.
HTH,
Subscribe to:
Post Comments (Atom)
Thank you very much it is worked for me........
ReplyDeleteGlad to hear that it worked for you.
DeleteIt is not working for me :(
ReplyDeleteAre you getting the same error message, can you tell me about the problem, and send me the error message?
Deletevery nice
ReplyDeleteThx