删除发布

适用于:SQL Server Azure SQL 托管实例

本主题介绍如何使用 SQL Server Management Studio、Transact-SQL 或复制管理对象在 SQL Server 中删除发布。

本主题内容

使用 SQL Server Management Studio

从 SQL Server Management Studio 的“本地发布”文件夹中删除发布。

删除发布

  1. 在 Management Studio 中连接到发布服务器,然后展开服务器节点。

  2. 展开 “复制” 文件夹,再展开 “本地发布” 文件夹。

  3. 右键单击要删除的发布,然后单击 “删除”

“使用 Transact-SQL”

可以使用复制存储过程以编程方式删除发布。 使用的存储过程取决于要删除的发布的类型。

注意

删除发布并不会删除发布数据库中的已发布对象,也不会删除订阅数据库中的相应对象。 如有必要,使用 DROP <object> 命令手动删除这些对象。

删除快照发布或事务发布

  1. 执行下列操作之一:

    • 若要删除单个发布,请在发布服务器上,对发布数据库执行 sp_droppublication

    • 若要删除发布数据库中的所有发布并删除其中的所有复制对象,请在发布服务器上执行 sp_removedbreplication 。 为 @type 指定 tran值。 (可选)如果无法访问分发服务器,或者数据库的状态为可疑或脱机,则将 @force 的值指定为 1。 (可选)如果未对发布数据库执行 sp_removedbreplication ,则为 @dbname 指定数据库的名称。

      注意

      @force 的值指定为 1 可能会使与复制相关的发布对象保留在数据库中。

  2. (可选)如果此数据库中没有任何其他发布,则执行 sp_replicationdboption (Transact-SQL),以禁止当前数据库使用快照或事务复制进行发布。

  3. (可选)在订阅服务器上,对订阅数据库执行 sp_subscription_cleanup 以删除订阅数据库中的任何剩余复制元数据。

删除合并发布

  1. 执行下列操作之一:

    • 要删除单个发布,请在发布数据库中的发布服务器上执行 sp_dropmergepublication (Transact-SQL)

    • 若要删除发布数据库中的所有发布并删除其中的所有复制对象,请在发布服务器上执行 sp_removedbreplication 。 为 @type 指定 merge值。 (可选)如果无法访问分发服务器,或者数据库的状态为可疑或脱机,则将 @force 的值指定为 1。 (可选)如果未对发布数据库执行 sp_removedbreplication ,则为 @dbname 指定数据库的名称。

      注意

      @force 的值指定为 1 可能会使与复制相关的发布对象保留在数据库中。

  2. (可选)如果此数据库中没有其他发布,则执行 sp_replicationdboption (Transact-SQL),以禁止当前数据库使用合并复制进行发布。

  3. 在订阅数据库中的订阅服务器上,执行 sp_mergesubscription_cleanup (Transact-SQL),以删除订阅数据库中的任何剩余复制元数据。

示例 (Transact-SQL)

该示例演示如何删除事务发布并禁用数据库的事务发布。 该示例假定以前删除了所有订阅。 有关详细信息,请参阅 Delete a Pull SubscriptionDelete a Push Subscription

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks2022]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

该示例演示如何删除合并发布并禁用数据库的合并发布。 该示例假定以前删除了所有订阅。 有关详细信息,请参阅 Delete a Pull SubscriptionDelete a Push Subscription

DECLARE @publication AS sysname
DECLARE @publicationDB    AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 
SET @publicationDB = N'AdventureWorks'

-- Remove the merge publication.
USE [AdventureWorks]
EXEC sp_dropmergepublication @publication = @publication;

-- Remove replication objects from the database.
USE master
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'merge publish', 
  @value = N'false'
GO

使用复制管理对象 (RMO)

可以使用复制管理对象 (RMO) 以编程方式删除发布。 用于删除发布的 RMO 类取决于要删除的发布的类型。

删除快照发布或事务发布

  1. 使用 ServerConnection 类创建与发布服务器的连接。

  2. 创建 TransPublication 类的一个实例。

  3. 设置发布的 NameDatabaseName 属性,并将 ConnectionContext 属性设置为步骤 1 中创建的连接。

  4. 请检查 IsExistingObject 属性以验证发布是否存在。 如果此属性的值为 false,则步骤 3 中的发布属性定义不正确,或者发布不存在。

  5. 调用 Remove 方法。

  6. (可选)如果此数据库中不存在其他事务发布,则可按照下面的步骤为事务发布禁用此数据库:

    1. 创建 ReplicationDatabase 类的一个实例。 将 ConnectionContext 属性设置为步骤 1 中的 ServerConnection 实例。

    2. 调用 LoadProperties 方法。 如果该方法返回 false,请确认数据库是否存在。

    3. 设置发布的 EnabledTransPublishing 属性设置为 false文件夹中删除发布。

    4. 调用 CommitPropertyChanges 方法。

  7. 关闭连接。

删除合并发布

  1. 使用 ServerConnection 类创建与发布服务器的连接。

  2. 创建 MergePublication 类的一个实例。

  3. 设置发布的 NameDatabaseName 属性,并将 ConnectionContext 属性设置为步骤 1 中创建的连接。

  4. 请检查 IsExistingObject 属性以验证发布是否存在。 如果此属性的值为 false,则步骤 3 中的发布属性定义不正确,或者发布不存在。

  5. 调用 Remove 方法。

  6. (可选)如果此数据库中不存在其他合并发布,则可按照下面的步骤为合并发布禁用此数据库:

    1. 创建 ReplicationDatabase 类的一个实例。 将 ConnectionContext 属性设置为步骤 1 中的 ServerConnection 实例。

    2. 调用 LoadProperties 方法。 如果该方法返回 false,请验证数据库是否存在。

    3. 设置发布的 EnabledMergePublishing 属性设置为 false文件夹中删除发布。

    4. 调用 CommitPropertyChanges 方法。

  7. 关闭连接。

示例 (RMO)

下面的示例删除事务发布。 如果此数据库不存在其他事务发布,则事务发布也被禁用。

// Define the Publisher, publication database, 
// and publication names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2022";

TransPublication publication;
ReplicationDatabase publicationDb;

// Create a connection to the Publisher 
// using Windows Authentication.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    conn.Connect();

    // Set the required properties for the transactional publication.
    publication = new TransPublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    // Delete the publication, if it exists and has no subscriptions.
    if (publication.LoadProperties() && !publication.HasSubscription)
    {
        publication.Remove();
    }
    else
    {
        // Do something here if the publication does not exist
        // or has subscriptions.
        throw new ApplicationException(String.Format(
            "The publication {0} could not be deleted. " +
            "Ensure that the publication exists and that all " +
            "subscriptions have been deleted.",
            publicationName, publisherName));
    }

    // If no other transactional publications exists,
    // disable publishing on the database.
    publicationDb = new ReplicationDatabase(publicationDbName, conn);
    if (publicationDb.LoadProperties())
    {
        if (publicationDb.TransPublications.Count == 0)
        {
            publicationDb.EnabledTransPublishing = false;
        }
    }
    else
    {
        // Do something here if the database does not exist.
        throw new ApplicationException(String.Format(
            "The database {0} does not exist on {1}.",
            publicationDbName, publisherName));
    }
}
catch (Exception ex)
{
    // Implement application error handling here.
    throw new ApplicationException(String.Format(
        "The publication {0} could not be deleted.",
        publicationName), ex);
}
finally
{
    conn.Disconnect();
}
' Define the Publisher, publication database, 
' and publication names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2022"

Dim publication As TransPublication
Dim publicationDb As ReplicationDatabase

' Create a connection to the Publisher 
' using Windows Authentication.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    conn.Connect()

    ' Set the required properties for the transactional publication.
    publication = New TransPublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' Delete the publication, if it exists and has no subscriptions.
    If publication.LoadProperties() And Not publication.HasSubscription Then
        publication.Remove()
    Else
        ' Do something here if the publication does not exist
        ' or has subscriptions.
        Throw New ApplicationException(String.Format( _
         "The publication {0} could not be deleted. " + _
         "Ensure that the publication exists and that all " + _
         "subscriptions have been deleted.", _
         publicationName, publisherName))
    End If

    ' If no other transactional publications exists,
    ' disable publishing on the database.
    publicationDb = New ReplicationDatabase(publicationDbName, conn)
    If publicationDb.LoadProperties() Then
        If publicationDb.TransPublications.Count = 0 Then
            publicationDb.EnabledTransPublishing = False
        End If
    Else
        ' Do something here if the database does not exist.
        Throw New ApplicationException(String.Format( _
         "The database {0} does not exist on {1}.", _
         publicationDbName, publisherName))
    End If
Catch ex As Exception
    ' Implement application error handling here.
    Throw New ApplicationException(String.Format( _
     "The publication {0} could not be deleted.", _
     publicationName), ex)
Finally
    conn.Disconnect()
End Try

下面的示例删除合并发布。 如果此数据库不存在其他合并发布,则合并发布也被禁用。

// Define the Publisher, publication database, 
// and publication names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2022";

MergePublication publication;
ReplicationDatabase publicationDb;

// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Set the required properties for the merge publication.
    publication = new MergePublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    // Delete the publication, if it exists and has no subscriptions.
    if (publication.LoadProperties() && !publication.HasSubscription)
    {
        publication.Remove();
    }
    else
    {
        // Do something here if the publication does not exist
        // or has subscriptions.
        throw new ApplicationException(String.Format(
            "The publication {0} could not be deleted. " +
            "Ensure that the publication exists and that all " +
            "subscriptions have been deleted.",
            publicationName, publisherName));
    }

    // If no other merge publications exists,
    // disable publishing on the database.
    publicationDb = new ReplicationDatabase(publicationDbName, conn);
    if (publicationDb.LoadProperties())
    {
        if (publicationDb.MergePublications.Count == 0 && publicationDb.EnabledMergePublishing)
        {
            publicationDb.EnabledMergePublishing = false;
        }
    }
    else
    {
        // Do something here if the database does not exist.
        throw new ApplicationException(String.Format(
            "The database {0} does not exist on {1}.",
            publicationDbName, publisherName));
    }
}
catch (Exception ex)
{
    // Implement application error handling here.
    throw new ApplicationException(String.Format(
        "The publication {0} could not be deleted.",
        publicationName), ex);
}
finally
{
    conn.Disconnect();
}
' Define the Publisher, publication database, 
' and publication names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"

Dim publication As MergePublication
Dim publicationDb As ReplicationDatabase

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for the merge publication.
    publication = New MergePublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' Delete the publication, if it exists and has no subscriptions.
    If (publication.LoadProperties() And Not publication.HasSubscription) Then
        publication.Remove()
    Else
        ' Do something here if the publication does not exist
        ' or has subscriptions.
        Throw New ApplicationException(String.Format( _
         "The publication {0} could not be deleted. " + _
         "Ensure that the publication exists and that all " + _
         "subscriptions have been deleted.", _
         publicationName, publisherName))
    End If

    ' If no other merge publications exists,
    ' disable publishing on the database.
    publicationDb = New ReplicationDatabase(publicationDbName, conn)
    If publicationDb.LoadProperties() Then
        If publicationDb.MergePublications.Count = 0 _
        And publicationDb.EnabledMergePublishing Then
            publicationDb.EnabledMergePublishing = False
        End If
    Else
        ' Do something here if the database does not exist.
        Throw New ApplicationException(String.Format( _
         "The database {0} does not exist on {1}.", _
         publicationDbName, publisherName))
    End If
Catch ex As Exception
    ' Implement application error handling here.
    Throw New ApplicationException(String.Format( _
     "The publication {0} could not be deleted.", _
     publicationName), ex)
Finally
    conn.Disconnect()
End Try

另请参阅

Replication System Stored Procedures Concepts
发布数据和数据库对象