Share via

Delete a Publication

  • Article

This topic describes how to delete a publication in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

In This Topic

Using SQL Server Management Studio

Delete publications from the Local Publications folder in SQL Server Management Studio.

To delete a publication

  1. Connect to the Publisher in Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click the publication you want to delete, and then click Delete.

Using Transact-SQL

Publications can be deleted programmatically using replication stored procedures. The stored procedures that you use depend on the type of publication being deleted.

Note

Deleting a publication does not remove published objects from the publication database or the corresponding objects from the subscription database. Use the DROP <object> command to manually remove these objects if necessary.

To delete a snapshot or transactional publication

  1. Do one of the following:

    • To delete a single publication, execute sp_droppublication at the Publisher on the publication database.

    • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of tran for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      Note

      Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using snapshot or transactional replication.

  3. (Optional) At the Subscriber on the subscription database, execute sp_subscription_cleanup to remove any remaining replication metadata in the subscription database.

To delete a merge publication

  1. Do one of the following:

    • To delete a single publication, execute sp_dropmergepublication (Transact-SQL) at the Publisher on the publication database.

    • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of merge for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      Note

      Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.

  3. (Optional) At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL) to remove any remaining replication metadata in the subscription database.

Examples (Transact-SQL)

This example shows how to remove a transactional publication and disable transactional publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see Delete a Pull Subscription or Delete a Push Subscription.

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

-- Remove a transactional publication.
USE [AdventureWorks2012]
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

This example shows how to remove a merge publication and disable merge publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see Delete a Pull Subscription or Delete 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

Using Replication Management Objects (RMO)

You can delete publications programmatically by using Replication Management Objects (RMO). The RMO classes that you use to remove a publication depend on the type of publication you remove.

To remove a snapshot or transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransPublication class.

  3. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  4. Check the IsExistingObject property to verify that the publication exists. If the value of this property is false, either the publication properties in step 3 were defined incorrectly or the publication does not exist.

  5. Call the Remove method.

  6. (Optional) If no other transactional publications exist for this database, the database can be disabled for transactional publishing as follows:

    1. Create an instance of the ReplicationDatabase class. Set the ConnectionContext property to the instance of ServerConnection from step 1.

    2. Call the LoadProperties method. If this method returns false, confirm that the database exists.

    3. Set the EnabledTransPublishing property to false.

    4. Call the CommitPropertyChanges method.

  7. Close the connections.

To remove a merge publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergePublication class.

  3. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  4. Check the IsExistingObject property to verify that the publication exists. If the value of this property is false, either the publication properties in step 3 were defined incorrectly or the publication does not exist.

  5. Call the Remove method.

  6. (Optional) If no other merge publications exist for this database, the database can be disabled for merge publishing as follows:

    1. Create an instance of the ReplicationDatabase class. Set the ConnectionContext property to the instance of ServerConnection from Step 1.

    2. Call the LoadProperties method. If this method returns false, verify that the database exists.

    3. Set the EnabledMergePublishing property to false.

    4. Call the CommitPropertyChanges method.

  7. Close the connections.

Examples (RMO)

The following example deletes a transactional publication. If no other transactional publications exist for this database, transactional publishing is also disabled.

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

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 = "AdventureWorks2012"

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

The following example deletes a merge publication. If no other merge publications exist for this database, merge publishing is also disabled.

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

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 = "AdventureWorks2012"

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

See Also

Replication System Stored Procedures Concepts
Publish Data and Database Objects