Restore - Replication Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore - Replication Problem

Author

Topic

pontifikas
Starting Member

32 Posts

Posted - 2004-12-29 : 04:13:26

SQLServer 2000

I have a database, succesfully replicated to another server.

I got a backup of the database in order to create a temporary one and work on it without affecting my original.

So i restored the backup upon a new db I created(with a different name than the original).

The problem is that the new one appears to be replicated(it appears to
have publications)while it is not.

As a result I cannot drop it, or drop any table or column in it.
Whenever I try drop sth I get the message
"Cannot drop blah blah blah , it is being used for replication"

Furthermore I get errors when trying to disable publications or subscriptions.

Any Ideas?

Thanks in advance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-29 : 04:34:53

1. did you restore on a different machine?
2. publications and subscriptions where created after restore?
3. which object can't you drop?
4. what errors do you get when trying to disable the publication or subscription?

--------------------
keeping it simple...

pontifikas
Starting Member

32 Posts

Posted - 2004-12-29 : 04:47:26

1)No it is on the same machine
2)Publications and subscriptions existed.As I said , the database is replicated.I just took a backup of it and have restored it on another, new, database(on the same machine).
3)I cannot drop anything

(Tables, columns, the whole database)
4)
When I go to Tools->Replication->Disable Publising wizard, I get a message
"Server encountered one or more errors while retrieving the list
of databases and publications on 'Sqlserver'.This list may not be complete Error 208:General error"

I continue and indeed on the list of the publications, the restored database appears not.However in the Enterprise Manager,this database does have this "blue hand" symbol which indicates that there is a publication of it(probably inherited from the backup).

In the end of the wizard i get this message
"SQL could not disable publishing and distribution on 'Sqlserver' Error 208: Invalid object name 'syssubscriptions'"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-30 : 03:21:54

in that case, don't disable publishing on the server. it's weird coz i already did a restore of a published database on same server but haven't encountered your problem.

check if the database is being published under replication monitor or issue a sp_helppublication under the new database
what do you intend to do? there might be some workaround.

--------------------
keeping it simple...

pontifikas
Starting Member

32 Posts

Posted - 2005-01-03 : 03:32:45

I thing I found something on the web about this.
But I'm not sure if it works since a new problem arose(I'll make a new thread for this).Should I resolve this too I'll write the solution in detail.
Thanks for you advice anyway :)

pontifikas
Starting Member

32 Posts

Posted - 2005-01-03 : 05:24:36

OK.
Here is what I did:

1)exec sp_configure 'allow updates',1
go
reconfigure with override

2) update sysdatabases set category=0 where name='databasename'

3)exec sp_replicationdboption N'Databasename', N'publish', N'false'

4)From the Enterprise Manager we go Tools->Replication->Disable Publishing

This way I managed to delete the whole table.

BUT.....After I restore the db again, I cannot delete certain tables.
I get the same message again(table is being used for replication), eventhough replication is disabled at my system(and at the restored db).

What is special about those tables is that they have as an owner, not [dbo] but another user.
Those owned by dbo, I can delete.

This user exists in my db,and is the name my program uses to login to the Server.Somehow though, the restored db acts as if it is an unknown one.

So,I cannot delete the user because it owns these tables, I cannot delete the tables because they are used for replication->

->My program cannot login to the db.

Sorry for being so messed up but you realize how serious this might get, should my clients try to use a restored db due to any kaind of problem.

pontifikas
Starting Member

32 Posts

Posted - 2005-01-03 : 06:16:28

Is there a way to perform a "violent" delete? Somethng like a KILL.TABLE comand?
I dont need those tables and I want them dead, no matter the cost.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-03 : 06:54:40

so you only need to restore some tables and not all?

found this in BOL

quote:

Restoring Backups of Replicated Databases to a Different Server or Database
When you restore a backup of a replicated database to a server or database other than the one on which it was created, your replication settings cannot be preserved. For publishing databases and merge subscribing databases, a full restore of the database and logs is followed by an automatic removal of replication meta data from the database when the database or server you restore to differs from the one on which the backup was created. If necessary, you can use this approach to recover your data to another server or database and then set up a new replication topology including the restored database.

from my understanding, replication settings should not have been preserved coz you restored in a different database.

can you change the ownership of the tables to dbo?
sp_changeobjectowner '[user].objectname','dbo'
then delete the tables?

--------------------
keeping it simple...

pontifikas
Starting Member

32 Posts

Posted - 2005-01-03 : 08:06:49

Cheers mate

I tried to change ownership before but I was using wrong arguments(did not include [user]. before the object name.

I can now login to the db.
But still I cannot delete the tables because they are used for replication....

I dont care though.They are useless anyway.
But it is queer indeed.For replication seems to preserve itself during restore.

One Big thanks again!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 05:39:08

can you delete the column used for replication?

this is the most violent step i can think of

since you don't need replication.

--------------------
keeping it simple...

pontifikas
Starting Member

32 Posts

Posted - 2005-01-05 : 08:03:20

quote:
Originally posted by jen

can you delete the column used for replication?

this is the most violent step i can think of since you don't need replication.

No Can't do.
I cannot alter the table because it's being used for replication

PIbison
Starting Member

1 Post

Posted - 2005-01-14 : 04:30:32

There is a stored procedure to do this called sp_MSunmarkreplinfo which takes a tablename as a parameter. Alternatively, setting replinfo to 0 in sysobjects for the particular table should do it. Finally, running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is not also configured as a publisher.
HTH,
Paul Ibison (SQL Server MVP)

srinimukka
Starting Member

1 Post

Posted - 2008-06-11 : 08:05:15

We have restored a database where it was being replicated on to our local server. When we try to delete one of the procedure, the message that we get is "cannot drop the procedure because its being used for replication".On checking the sys.objects, for this procedure, it is shown as is_schema_publised=1. On trying to update this, it says adhoc updates are not allowed. I have deleted the replication on the original database long back . How should we handle this.

Regards,
Srinvias M

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-11 : 13:08:53

Did you try with sp_removedbreplication ?

mindreader13
Starting Member

6 Posts

Posted - 2008-10-15 : 02:55:49

did anyone have solution on this? i encounter the same problem

CCURCILLO
Starting Member

1 Post

Posted - 2011-05-25 : 12:23:31

EXEC sp_removedbreplication 'YourDBName'