Jan
14
2013

How to drop Publisher database in Transactional Replication

Dropping publisher database may be required in various scenarios; please find steps which can help you to drop in case you are facing issues with it.

I am including roll back approach as well, it helps sometimes.

 

First step -> Drop Publication and subscription

Second Step -> Drop Database

 

First step,

  • Right click Publication > Generate Scripts > Generate Drop replication and objects script > Save script to local drive
  • Right click Publication > Generate Scripts > Generate Create replication and objects script > Save script to local drive

 

Now execute the drop replication script, it will remove subscription, remove all jobs, and remove publication

Issues&Resolution: if it throws some error, then try to stop all replication jobs related to this publication and run script again

 

Moving to second step,

 

Here you may face some issues, so please do following first,

  • Right click database (to be restored) > Script database as > Drop to > File… > save it on local folder
  • Right click database (to be restored) > Script database as > Create to > File… > save it on local folder

     

    Now, open script to drop database in SSMS and execute it, it may throw following error,

    Server: Msg 3724, Level 16, State 3, Line 1

    Cannot drop the database XXX because it is being used for replication.

     

    Issue&Resolution: Although publication is dropped but sometimes it may not update the system tables, so execute following,

    exec sp_removedbreplication ‘XXX’

    go

    exec sp_dboption ‘XXX’,’published’,false

    go

     

    Now, try to drop database, if it still throws some error then do following,

        

    Update system catalogue and then drop database by executing following,

    Use [Master]

    Go

    sp_configure ‘allow updates’,1
    reconfigure with override
    GO
    UPDATE sysdatabases set Category = 0 where dbid = <your database DBID>
    GO
    sp_configure ‘allow updates’,0
    reconfigure with override

    GO

    Drop database <database name>

    Go

 

    If above not work then alternatively try this,

    Take the database Offline and then delete mdf and ldf files and then delete database

 

Once database is dropped, you can recreate blank one using create script, it will create a blank database.

 

These ways may come handy in several situations where you need to refresh QA and Dev environments.

 

References:

http://www.sqlservercentral.com/Forums/Topic115333-7-3.aspx

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: