게시 삭제Delete a Publication

이 항목에서는 SQL Server 2017SQL Server 2017 , SQL Server Management StudioSQL Server Management Studio또는 RMO(복제 관리 개체)를 사용하여 Transact-SQLTransact-SQL에서 게시를 삭제하는 방법에 대해 설명합니다.This topic describes how to delete a publication in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO).

항목 내용In This Topic

SQL Server Management Studio 사용 Using SQL Server Management Studio

로컬 게시 SQL Server Management StudioSQL Server Management Studio폴더에서 게시를 삭제합니다.Delete publications from the Local Publications folder in SQL Server Management StudioSQL Server Management Studio.

게시를 삭제하려면To delete a publication

  1. Management StudioManagement Studio에서 게시자에 연결한 다음 해당 서버 노드를 확장합니다.Connect to the Publisher in Management StudioManagement 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.

Transact-SQL 사용 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.

참고

게시를 삭제해도 게시 데이터베이스의 게시된 개체 또는 구독 데이터베이스의 해당 개체는 제거되지 않습니다.Deleting a publication does not remove published objects from the publication database or the corresponding objects from the subscription database. 필요한 경우 DROP <object> 명령을 사용하여 이러한 개체를 수동으로 제거할 수 있습니다.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:

    • 단일 게시를 삭제하려면 게시 데이터베이스의 게시자에서 sp_droppublication 을 실행합니다.To delete a single publication, execute sp_droppublication at the Publisher on the publication database.

    • 모든 게시를 삭제하고 게시된 데이터베이스에서 모든 복제 개체를 제거하려면 게시자에서 sp_removedbreplication 을 실행합니다.To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. @typetran 값을 지정합니다.Specify a value of tran for @type. (옵션) 배포자에 액세스할 수 없거나 데이터베이스의 상태가 주의 대상 또는 오프라인인 경우 @force @type @force폴더에서 게시를 삭제합니다.(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. (옵션) 게시 데이터베이스에서 @dbname 이 실행되지 않은 경우 sp_removedbreplication 에 데이터베이스 이름을 지정합니다.(Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      참고

      @force1 값을 지정하면 데이터베이스에 복제 관련 게시 개체가 남을 수 있습니다.Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (선택 사항) 이 데이터베이스에 다른 게시가 없으면 sp_replicationdboption(Transact-SQL)을 실행하여 스냅숏 또는 트랜잭션 복제를 통해 현재 데이터베이스를 게시할 수 없도록 해제합니다.(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. (옵션) 구독 데이터베이스의 구독자에서 sp_subscription_cleanup 을 실행하여 구독 데이터베이스에 남은 모든 복제 메타데이터를 제거합니다.(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:

    • 단일 게시를 삭제하려면 게시 데이터베이스의 게시자에서 sp_dropmergepublication(Transact-SQL)을 실행합니다.To delete a single publication, execute sp_dropmergepublication (Transact-SQL) at the Publisher on the publication database.

    • 모든 게시를 삭제하고 게시된 데이터베이스에서 모든 복제 개체를 제거하려면 게시자에서 sp_removedbreplication 을 실행합니다.To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. @typemerge 값을 지정합니다.Specify a value of merge for @type. (옵션) 배포자에 액세스할 수 없거나 데이터베이스의 상태가 주의 대상 또는 오프라인인 경우 @force @type @force폴더에서 게시를 삭제합니다.(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. (옵션) 게시 데이터베이스에서 @dbname 이 실행되지 않은 경우 sp_removedbreplication 에 데이터베이스 이름을 지정합니다.(Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      참고

      @force1 값을 지정하면 데이터베이스에 복제 관련 게시 개체가 남을 수 있습니다.Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (선택 사항) 이 데이터베이스에 다른 게시가 없으면 sp_replicationdboption(Transact-SQL)을 실행하여 병합 복제를 통해 현재 데이터베이스를 게시할 수 없도록 해제합니다.(Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.

  3. (선택 사항) 구독 데이터베이스의 구독자에서 sp_mergesubscription_cleanup(Transact-SQL)을 실행하여 구독 데이터베이스에 남은 모든 복제 메타데이터를 제거합니다.(Optional) At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL) to remove any remaining replication metadata in the subscription database.

예(Transact-SQL) 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. 자세한 내용은 Delete a Pull Subscription 또는 Delete a Push Subscription를 참조하세요.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. 자세한 내용은 Delete a Pull Subscription 또는 Delete a Push Subscription를 참조하세요.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

RMO(복제 관리 개체) 사용 Using Replication Management Objects (RMO)

RMO(복제 관리 개체)를 사용하여 프로그래밍 방식으로 게시를 삭제할 수 있습니다.You can delete publications programmatically by using Replication Management Objects (RMO). 게시를 제거하는 데 사용하는 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. ServerConnection 클래스를 사용하여 게시자 연결을 만듭니다.Create a connection to the Publisher by using the ServerConnection class.

  2. TransPublication 클래스의 인스턴스를 만듭니다.Create an instance of the TransPublication class.

  3. 게시에 대해 NameDatabaseName 속성을 설정하고 ConnectionContext 속성을 1단계에서 만든 연결로 설정합니다.Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  4. IsExistingObject 속성을 통해 게시가 존재하는지 확인합니다.Check the IsExistingObject property to verify that the publication exists. 이 속성의 값이 false이면 3단계의 게시 속성이 올바르게 정의되지 않았거나 게시가 없는 것입니다.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. Remove 메서드를 호출합니다.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. ReplicationDatabase 클래스의 인스턴스를 만듭니다.Create an instance of the ReplicationDatabase class. ConnectionContext 속성을 1단계에서 만든 ServerConnection 인스턴스로 설정합니다.Set the ConnectionContext property to the instance of ServerConnection from step 1.

    2. LoadProperties 메서드를 호출합니다.Call the LoadProperties method. 이 메서드가 false를 반환할 경우 데이터베이스가 있는지 확인합니다.If this method returns false, confirm that the database exists.

    3. 게시에 대해 EnabledTransPublishing 속성을 false폴더에서 게시를 삭제합니다.Set the EnabledTransPublishing property to false.

    4. CommitPropertyChanges 메서드를 호출합니다.Call the CommitPropertyChanges method.

  7. 연결을 닫습니다.Close the connections.

병합 게시를 제거하려면To remove a merge publication

  1. ServerConnection 클래스를 사용하여 게시자 연결을 만듭니다.Create a connection to the Publisher by using the ServerConnection class.

  2. MergePublication 클래스의 인스턴스를 만듭니다.Create an instance of the MergePublication class.

  3. 게시에 대해 NameDatabaseName 속성을 설정하고 ConnectionContext 속성을 1단계에서 만든 연결로 설정합니다.Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  4. IsExistingObject 속성을 통해 게시가 존재하는지 확인합니다.Check the IsExistingObject property to verify that the publication exists. 이 속성의 값이 false이면 3단계의 게시 속성이 올바르게 정의되지 않았거나 게시가 없는 것입니다.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. Remove 메서드를 호출합니다.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. ReplicationDatabase 클래스의 인스턴스를 만듭니다.Create an instance of the ReplicationDatabase class. ConnectionContext 속성을 1단계에서 만든 ServerConnection 인스턴스로 설정합니다.Set the ConnectionContext property to the instance of ServerConnection from Step 1.

    2. LoadProperties 메서드를 호출합니다.Call the LoadProperties method. 이 메서드가 false를 반환하면 데이터베이스가 있는지 확인합니다.If this method returns false, verify that the database exists.

    3. 게시에 대해 EnabledMergePublishing 속성을 false폴더에서 게시를 삭제합니다.Set the EnabledMergePublishing property to false.

    4. CommitPropertyChanges 메서드를 호출합니다.Call the CommitPropertyChanges method.

  7. 연결을 닫습니다.Close the connections.

예(RMO) 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