Restored Replicated DB to non-replicated location

Last Post 08 Jul 2004 02:00 PM by jaa. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jaa
New Member
New Member

--
08 Jul 2004 11:13 AM
Hello,

I have restored a recent backup of one of our replicated production databases to our test server. In the test environment, I do not want replication to occur, and if you look at the database in Enterprise Manager there is no publication set up. However, when our developer tries to alter the structure of one of the tables, he is getting an error 'Cannot alter table X because it is being published for replication.'

I have tried running some of the system stored procedures such as sp_droppublication and sp_droparticle, but it fails because syspublications, etc. do not exist. I found that the replinfo field in sysobjects is set to '1' on all of the replicated tables, so as a last resort I tried updating this to '0'. After that, the error changed to 'Cannot alter COLUMN X because it is being REPLICATED.' Evidently there is some other flag out there that is making the SQL Server think this table is still being replicated.

Has anyone run into this and how do you fix it? Is my only option to drop the restored database, and copy all the database objects down using the DTS tools? This could take awhile because of some large tables, but it could be done.

BTW, I have also checked the sysdatabases table in master and the category is set to 0, so I believe this is definitely at a table level somewhere within the database.

Thanks,
Jill
jaa
New Member
New Member

--
08 Jul 2004 02:00 PM
Thanks for your reply. I tried what you suggested, but it reported back that the Current Setting for the Published option is 'Off'.

sp_dboption 'DatabaseX', 'Published'

results:

OptionName CurrentSetting
published off

Just to make sure, I also checked the 'merge publish' option even though we use transactional, and it was 'off' as well.

Any other ideas? In order to get our testing underway, I went ahead and dropped the database, created a new empty database, and copied all the objects over, but it took about 4 times longer than the backup/restore option (50 min vs. 12min, so not terrible, but not ideal). It also failed at the very end without a good error message, and of course, I had not thought to put an error log on the job. It appears that all the objects are there, but I'm not comfortable with the fact that it failed. I had to use the Copy Object wizard with the 'copy all objects' option, because, apparently, the copy database wizard cannot be used on replicated databases either. When I tried, that database was not available to select and the description said 'replicated database'.

Jill
You are not authorized to post a reply.

Acceptable Use Policy