删除sqlserver发布数据库

发现发布无法删除的情况，于是执行

1、sp_helpdistpublisher列出发布的服务器名和发布数据库

2、sp_helpsubscriberinfo 列出所有subscribers.

每一个都执行sp_dropsubscriber 'SubscriberName'

3、sp_helppublisher

执行 sp_dropdistpublisher  'name'
执行这一步后发现错误 Invalid object name 'dbo.MSmerge_agents'.

 

4、修复第3步的错误，按表语句在distribution建表

create table msmerge_agents

(id int, name nvarchar(100),

publisher_id smallint,

publisher_db sysname, publication sysname,

subscriber_id smallint, subscriber_db sysname,

local_job bit, job_id binary(16),

profile_id int, anonymous_subid uniqueidentifier,

subscriber_name sysname,

creation_date datetime,

offload_enabled bit,

offload_server sysname,

sid varbinary(85),

subscriber_security_mode smallint,

subscriber_password nvarchar(524),

publisher_login sysname,

publisher_password nvarchar(524),

job_step_uid uniqueidentifier

)

CREATE table MSmerge_subscriptions

(

publisher_id smallint,

publisher_db sysname,

publication_id int,

subscriber_id smallint,

subscriber_db sysname,

subscription_type int,

sync_type tinyint,

status tinyint,

subscription_time datetime

)
5、执行

exec sp_dropdistributiondb @database = N'distribution'
GO

执行过程中可中断，数据库distribution由系统数据库变成了用户数据库，选择数据库删除。