sql server 复制订阅的坑
公司系统更新，生产库新增加了几张表，需要同步到同步库，通过ssms 界面操作后，无法进行数据同步
<<案例总结>>
问题定义
=======
SQL Server 2012重新初始化订阅，启动代理，提示未生产快照
排查方法
查询发布状态，观察是否有同步的表，如果没有，运行下面命令
=======
- 首先我们检查了您环境中Publication端，确认没有同步的数据表已经加入发布，但是在订阅服务器的数库中无法获取同步数据。检查Agent没有报错信息。之后我们尝试使用如下语句重新将该Article从发布端移除。
Drop table from the publication. (This is just a sample, you can skip it, if you don’t want to drop this table, you can directly add a new article)
USE Pub_DB
exec sp_dropsubscription @publication = N'backpub',
@article = N'test',
@subscriber = N'all',
@destination_db = N'all'
GO
exec sp_droparticle @publication = N'backpub',
@article = N'test',
@force_invalidate_snapshot = 1
GO
- 添加Article到发布端，语句如下所示：
USE Pub_DB
EXEC sp_addarticle @publication = N'backpub',
@article = N'test',
@source_object=N'test',
@force_invalidate_snapshot=1
GO
[ @force_invalidate_snapshot = ] force_invalidate_snapshot Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.
0 specifies that adding an article does not cause the snapshot to be invalid. If the stored procedure detects that the change requires a new snapshot, an error occurs and no changes are made.
1 specifies that adding an article may cause the snapshot to be invalid, and if subscriptions exist that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot to be generated.
关于sp_addarticle的详细信息，您可以参照如下链接;
- 使用如下方法添加Article到订阅端。
<Optional>Then add the subscription for this new article using the following command
EXEC sp_addsubscription @publication = 'backpub', --Publication name
@article = 'test', --article name
@subscriber ='houdu533VM', --this is the subscriber server name\instance name
@destination_db = 'sub_back', --subscriber DB name
@reserved='Internal'
说明微软的ssms 界面还是不靠谱
