sp_removedbreplication (Transact-SQL)

sp_removedbreplication (Transact-SQ...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

sp_removedbreplication (Transact-SQL)

This stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database. The procedure removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database.

Note:
This procedure should be used only if other methods of removing replication objects have failed. For more information about these methods, see Removing Replication.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @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.


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

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Example

-- 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

Permissions

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

Other Resources

How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)
Removing Replication
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance