Skip to content

Latest commit

 

History

History
156 lines (93 loc) · 11.7 KB

delete-a-publication.md

File metadata and controls

156 lines (93 loc) · 11.7 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Delete a Publication
Learn how to delete a publication in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
MashaMSFT
mathoma
09/25/2024
sql
replication
how-to
updatefrequency5
removing publications
publications [SQL Server replication], deleting
articles [SQL Server replication], deleting
deleting publications
=azuresqldb-mi-current||>=sql-server-2016

Delete a Publication

[!INCLUDEsql-asdbmi] This topic describes how to delete a publication in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or Replication Management Objects (RMO).

In This Topic

Using SQL Server Management Studio

Delete publications from the Local Publications folder in [!INCLUDEssManStudioFull].

To delete a publication

  1. Connect to the Publisher in [!INCLUDEssManStudio], 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.

:::code language="sql" source="../codesnippet/tsql/delete-a-publication_1.sql":::

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.

:::code language="sql" source="../codesnippet/tsql/delete-a-publication_2.sql":::

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 xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.TransPublication class.

  3. Set the xref:Microsoft.SqlServer.Replication.Publication.Name%2A and xref:Microsoft.SqlServer.Replication.Publication.DatabaseName%2A properties for the publication, and set the xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A property to the connection created in step 1.

  4. Check the xref:Microsoft.SqlServer.Replication.ReplicationObject.IsExistingObject%2A 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 xref:Microsoft.SqlServer.Replication.Publication.Remove%2A 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 xref:Microsoft.SqlServer.Replication.ReplicationDatabase class. Set the xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A property to the instance of xref:Microsoft.SqlServer.Management.Common.ServerConnection from step 1.

    2. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method. If this method returns false, confirm that the database exists.

    3. Set the xref:Microsoft.SqlServer.Replication.ReplicationDatabase.EnabledTransPublishing%2A property to false.

    4. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.CommitPropertyChanges%2A method.

  7. Close the connections.

To remove a merge publication

  1. Create a connection to the Publisher by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.MergePublication class.

  3. Set the xref:Microsoft.SqlServer.Replication.Publication.Name%2A and xref:Microsoft.SqlServer.Replication.Publication.DatabaseName%2A properties for the publication, and set the xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A property to the connection created in step 1.

  4. Check the xref:Microsoft.SqlServer.Replication.ReplicationObject.IsExistingObject%2A 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 xref:Microsoft.SqlServer.Replication.Publication.Remove%2A 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 xref:Microsoft.SqlServer.Replication.ReplicationDatabase class. Set the xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A property to the instance of xref:Microsoft.SqlServer.Management.Common.ServerConnection from Step 1.

    2. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method. If this method returns false, verify that the database exists.

    3. Set the xref:Microsoft.SqlServer.Replication.ReplicationDatabase.EnabledMergePublishing%2A property to false.

    4. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.CommitPropertyChanges%2A 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.

[!code-csHowTo#rmo_DropTranPub]

[!code-vbHowTo#rmo_vb_DropTranPub]

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

[!code-csHowTo#rmo_DropMergePub]

[!code-vbHowTo#rmo_vb_DropMergePub]

Related content