Does anybody know how to unpublish the replicated databases without
going through deleting publication step by step? It can be done this
way but it's a pain when you need just to unpublish the database for
temporary to get some job done, and then to republish it. In Sql server
2000, I can just open table sysdatabases in master database, change the
number in category column from 1 to 0 to unpublish. After I am done
with that database, I just change it back from 0 to 1 to re-publish the
database. Does anybody know that trick in sql server 2005? Please !

Thanks,
Toan

Hi Toan,
I remember seeing this workaround a few times in the replication newsgroup.
There is always the worry that a subscriber will try to synchronize during
this 'offline' period and if possible, you'd want a more robust solution.
Mostly the method was proposed because someone wanted to detach the database
temporarily, but I think backup/restore would have worked. There are
distinctions between the 2 methods (detach can sometimes be used to have SQL
Server create a new log) but this was not mentioned as a requirement. I
wonder if this applies to your situation also? If not, please could you
explain a little more why you need access to detach rather than backup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Dear Paul,

I really appreciate your response to this topic. I need to temporarily
unpublish a replicated database to drop a table, for example, because I
cannot drop a table while it is being replicated. When it is
unpublished, I can drop a table, save it, and then re-publish.
Backup/Restore may have not worked for this situation.

The workaround solution in SQL Server 2000 worked perfectly for me in
the past when I worked with the old version. I'd like to know the same
trick for SQL Server 2005. Thank you in advance for any input you can
provide.

Thanks,
Toan

Paul Ibison wrote:
> Hi Toan,
> I remember seeing this workaround a few times in the replication newsgroup.
> There is always the worry that a subscriber will try to synchronize during
> this 'offline' period and if possible, you'd want a more robust solution.
> Mostly the method was proposed because someone wanted to detach the database
> temporarily, but I think backup/restore would have worked. There are
> distinctions between the 2 methods (detach can sometimes be used to have SQL
> Server create a new log) but this was not mentioned as a requirement. I
> wonder if this applies to your situation also? If not, please could you
> explain a little more why you need access to detach rather than backup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Hi Toan,
what I'm thinking is that can do this in a supported way if you're using
transactional replication.
There you could drop the subscriptions to the article then drop the article
from the publication (sp_dropsubscription followed by sp_droparticle) then
drop the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .