sp_removedbreplication (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_removedbreplication (Transact-SQL)

 

Applies To: SQL Server

This stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Execute in the appropriate database, or if the execution is in the context of another database on the same instance, specify the database where the replication objects should be removed. This procedure does not remove objects from other databases, such as the distribution database.

System_CAPS_ICON_note.jpg Note


This procedure should be used only if other methods of removing replication objects have failed.

Topic link icon Transact-SQL Syntax Conventions

  
sp_removedbreplication [ [ @dbname = ] 'dbname' ]  
    [ , [ @type = ] type ]   

[ @dbname=] 'dbname'
Is the name of the database. dbname is sysname, with a default value of NULL. When NULL, the current database will be used.

[ @type = ] type
Is the type of replication for which database objects are being removed. type is nvarchar(5) and can be one of the following values.

tranRemoves transactional replication publishing objects.
mergeRemoves merge replication publishing objects.
both (default)Removes all replication publishing objects.

0 (success) or 1 (failure)

sp_removedbreplication is used in all types of replication.

sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.

sp_removedbreplication cannot be used against a database that is marked as read-only.

-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2012Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

Only members of the sysadmin fixed server role can execute sp_removedbreplication.

-- Remove replication objects from the subscription database on MYSUB.  
DECLARE @subscriptionDB AS sysname  
SET @subscriptionDB = N'AdventureWorksReplica'  
  
-- Remove replication objects from a subscription database (if necessary).  
USE master  
EXEC sp_removedbreplication @subscriptionDB  
GO  
  

Disable Publishing and Distribution
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft