Dennis van der Stelt

Pingback from  University Update-Microsoft SQL Server-Cannot drop database because of replication

Thanks

it was very helpful. since few days i was facing this issue.

Worked a treat with SQL2000, I backed-up secondary PUBS to disk then restored, changing the name.

just wasted 3 hours and you helped me with a 2 second fix. Thanks

I faced the problem using SQL2000. Then I took the database offline without problems and I could delete the offline database. The data and log files had to be deleted manualy.

This works.. Thanks for your help!

I have used different named database, of the same database having equal db structure worked fine.Hope this helps. You need not have to create a new database!

The simple solution for me was to take offline and delete the files. Thanks for the tip.

right click on local subscriptions and click on subscripton properties in the right pane click on publication databases and unmark all the databases that are being used

You can also type:

use master

exec sp_removedbreplication YourDatabaseName

go

Thanks I spent 2 hours trying to remove a database that's replication partner no longer existed for. Your soultion worked. You rock!

Thanks a lot

Thanks for your help.

Thanx....

exec sp_removedbreplication works..

Autor      :             Noravia Rodriguez

Date        :             03/02/2010

Updates:             03/16/2010

Purpose:              How to unmark a database marked as replicated in SQL Server 2005

How to unmark a database marked as replicated in SQL Server 2005

1. Try:

EXEC sp_removeddbreplication “”DatabaseName”

GO

2. Try:

Take Database OffLine

Deatch

Attach Database Back

3. This tasks need to be done MANUALLY and in the following order:

i. Drop all existing subscriptions

ii. Drop all existing pubications

iii. Create a new publications with the same name

iv. Drop it again and say “Yes” to delete information on the Distributor (Distribution Database)

v. Verify Database has been unmark for replication

1. Go to the Database-Tasks-Detach and ensure that mark has been removed

OR

2. Run the following query and ensure “Category”=0           (RECOMMENDED)

SELECT name,category,* FROM master..sysdatabases

SQL Server 2000

a. Database

use master

GO

exec sp_replicationdboption @dbname = N'dbPOMS', @optname = N'publish', @value = N'false'

GO

b. Columns

use master

GO

exec sp_replicationdboption @dbname = N'dbPOMS', @optname = N'publish', @value = N'false'

GO

use [dbPOMS]

GO

sp_configure 'allow updates', 1

go

reconfigure with override

go

-- For Tables

UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0

go

-- For columns

UPDATE sysobjects SET repinfo=0 where repinfo=1

Go

sp_configure 'allow updates', 0

go

reconfigure with override

go

Was very helpful, it was a simple solution but no 1 usually tends to think on that side....

And you did so you are the 1 :)......

And about above comments removedb replication and all that it wont work if logshipping is broken and morever if ur missing the *.tuf file......

Please read the post before you all answer and make people go in wrong directions......

And also its next to impossible at times to update sys tables even with DAC connections.....

Anyways Nice post ......

Thanks

It woked just perfect!!!!

Please use this script to remove table value from sys.tables:

-- Removes Replication Flag for all Tables in the Database

-- using sp_MSunmarkreplinfo

SET NOCOUNT ON

DECLARE @tablename NVARCHAR(128)

DECLARE @RC INT

DECLARE curTable CURSOR FOR

SELECT [name] AS tbl

FROM sys.tables

OPEN curTable

FETCH NEXT FROM curTable

INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE @RC = dbo.sp_MSunmarkreplinfo @tablename

FETCH NEXT FROM curTable

INTO @tablename

END

CLOSE curTable

DEALLOCATE curTable

GO