创建SQL Server复制发布失败（错误22835）求助排查方案

• 备份恢复的数据库XXX存在CDC/复制元数据残留：原数据库曾配置过Change Data Capture（CDC）或复制，备份时相关元数据被包含在内；恢复后SQL Server Agent中虽无可见作业，但msdb系统库的作业关联表（如sysjobs、sysjobsteps）或目标数据库的CDC内部表（如cdc.cdc_jobs）仍残留无效的作业ID关联记录。
• 现有清理脚本不彻底：sp_removedbreplication和sp_cleanupdbreplication仅针对核心复制对象做清理，未覆盖CDC相关元数据及msdb中无效的作业关联记录，导致创建发布时触发的CDC作业删除逻辑找不到对应job_id，抛出14262/22835错误。

1. 清理目标数据库XXX的CDC元数据 #

USE XXX;
GO

-- 检查CDC启用状态
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'XXX';

-- 尝试禁用CDC（若执行失败，直接跳过执行后续清理）
EXEC sys.sp_cdc_disable_db;
GO

-- 手动清理CDC残留的作业关联记录
DELETE FROM cdc.cdc_jobs WHERE database_id = DB_ID('XXX');
DELETE FROM msdb.dbo.sysjobactivity WHERE job_id IN (SELECT job_id FROM cdc.cdc_jobs WHERE database_id = DB_ID('XXX'));
DELETE FROM msdb.dbo.sysjobhistory WHERE job_id IN (SELECT job_id FROM cdc.cdc_jobs WHERE database_id = DB_ID('XXX'));
GO

2. 清理msdb中无效的作业记录 #

USE msdb;
GO

-- 查找与XXX数据库关联的无效作业
SELECT j.job_id, j.name, s.database_name
FROM sysjobs j
JOIN sysjobsteps s ON j.job_id = s.job_id
WHERE s.database_name = 'XXX';

-- 批量删除这些无效作业
DECLARE @job_id UNIQUEIDENTIFIER;
DECLARE job_cursor CURSOR FOR
SELECT j.job_id
FROM sysjobs j
JOIN sysjobsteps s ON j.job_id = s.job_id
WHERE s.database_name = 'XXX';

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @job_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_delete_job @job_id = @job_id;
    FETCH NEXT FROM job_cursor INTO @job_id;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;
GO

3. 彻底清理复制元数据 #

USE distribution;
GO

-- 删除distribution库中XXX相关的复制记录
DELETE FROM MSpublisher_databases WHERE publisher_db = 'XXX';
DELETE FROM MSpublications WHERE publisher_db = 'XXX';
DELETE FROM MSarticles WHERE publisher_db = 'XXX';
DELETE FROM MSsubscriptions WHERE publisher_db = 'XXX';
GO

USE master;
GO

-- 再次执行复制清理脚本
EXEC sp_removedbreplication 'XXX';
EXEC sp_cleanupdbreplication;
GO

4. 验证并重新创建发布 #

完成清理后，确认以下内容：

• XXX数据库的is_cdc_enabled字段值为0
• msdb中无任何与XXX相关的作业记录
• distribution库中无XXX相关的复制元数据
  之后即可重新通过SQL Server Management Studio创建发布。

内容的提问来源于stack exchange，提问作者Dmitriy Ryabin