Adsense

Friday, January 3, 2014

Cleaning up a replicated database following a restore.

If you ever receive the error: "Cannot truncate table 'TABLENAME' because it is published for replication or enabled for Change Data Capture." but your database is not set up for replication it could be that the individual tables still think they're being replicated.  To see if this is the case run this query:
select * from sys.objects where is_published = 1
If you get any results then those tables think they are replicated.  To remove this replication run:
exec sp_removedbreplication 'DATABASENAME'
Warning!!  Verify you actually want to remove replication before doing this, once its gone it's gone and you'll need to resync.

I've noticed this behavior on occassion after backup up a replicated database and restoring it.  The restored version still has these flags set.

Cheers.