My database has a lot of subscribers to a lot of tables using Sql Server replication.
我的数据库有很多使用Sql Server复制的表的订阅者。
When I try to delete a table or delete a column on my database that takes part in a subscription on a published database the database migration fails.
当我试图删除一个表或删除数据库中参与已发布数据库订阅的列时,数据库迁移失败。
Those subscribers check if they publication is still live and if not they re-subscribe.
这些订阅者检查他们的发布是否仍然有效,如果没有,他们重新订阅。
I need to remove all publications from the current database via T-SQL so when I automate my database migrations delete columns or delete tables without the migration failing and needing manual intervention.
我需要通过T-SQL从当前数据库中删除所有发布,因此当我自动执行数据库迁移时,删除列或删除表,而不会出现迁移失败和需要人工干预的情况。
2 个解决方案
#1
4
To delete all transactional publications from a server run the following script on the database you are connected:
要从服务器上删除所有事务发布,请在连接的数据库上运行以下脚本:
declare @PublicationName varchar(max)
declare @ArticleName varchar(max)
declare @SubscriberServerName varchar(max)
declare @DestinationDb varchar(max)
IF OBJECT_ID('dbo.syspublications') is not null
BEGIN
DECLARE db_cursor CURSOR FOR
select
sp.name as PublicationName
,sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
, dest_db as DestinationDb
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dropping the transactional subscriptions
exec sp_dropsubscription @publication = @PublicationName, @subscriber = @SubscriberServerName, @destination_db = @DestinationDb, @article = N'all'
-- Dropping the transactional articles
exec sp_dropsubscription @publication = @PublicationName, @article = @ArticleName, @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = @PublicationName, @article = @ArticleName, @force_invalidate_snapshot = 1
-- Dropping the transactional publication
exec sp_droppublication @publication = @PublicationName
FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
#2
0
Run this procedure on database which you like to have clean for replication objects sp_removedbreplication http://technet.microsoft.com/en-us/library/ms188734%28v=sql.105%29.aspx
在您希望对复制对象sp_removedbreation (http://technet.microsoft.com/en-us/library/ms188734%)进行清理的数据库上运行此过程
#1
4
To delete all transactional publications from a server run the following script on the database you are connected:
要从服务器上删除所有事务发布,请在连接的数据库上运行以下脚本:
declare @PublicationName varchar(max)
declare @ArticleName varchar(max)
declare @SubscriberServerName varchar(max)
declare @DestinationDb varchar(max)
IF OBJECT_ID('dbo.syspublications') is not null
BEGIN
DECLARE db_cursor CURSOR FOR
select
sp.name as PublicationName
,sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
, dest_db as DestinationDb
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dropping the transactional subscriptions
exec sp_dropsubscription @publication = @PublicationName, @subscriber = @SubscriberServerName, @destination_db = @DestinationDb, @article = N'all'
-- Dropping the transactional articles
exec sp_dropsubscription @publication = @PublicationName, @article = @ArticleName, @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = @PublicationName, @article = @ArticleName, @force_invalidate_snapshot = 1
-- Dropping the transactional publication
exec sp_droppublication @publication = @PublicationName
FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
#2
0
Run this procedure on database which you like to have clean for replication objects sp_removedbreplication http://technet.microsoft.com/en-us/library/ms188734%28v=sql.105%29.aspx
在您希望对复制对象sp_removedbreation (http://technet.microsoft.com/en-us/library/ms188734%)进行清理的数据库上运行此过程