Removing Replication

• Peter Lee

  Grasshopper

  Points: 10

  More actions

  February 19, 2002 at 2:46 pm

  #157839

  We have a situation where we restored a database to another server (for development). The original database was set for transactional replication. The restored database, for whatever reason, continues to think its still replicating. Is there a way for me to remove the "replication" from the restored database because I would like to alter some columns in this database. It is currently telling me that it cannot drop the table due to replication, but we aren't using it. We are using SS2K.

  Thanks,

  Peter Lee

  CorSolutions Medical, Inc.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 19, 2002 at 3:08 pm

  #424154

  Try sp_removedbreplication 'yourdatabasenamehere'

• Peter Lee

  Grasshopper

  Points: 10

  More actions

  February 20, 2002 at 8:58 am

  #424174

  I tried that and it seemed successful, but I am still unable to alter the column. The tables in the database thinks that it is marked for publication, but the database does not. Any other suggestions?

  quote:

  ---

  Try sp_removedbreplication 'yourdatabasenamehere'

  ---

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 20, 2002 at 10:07 am

  #424182

  See if this doesn't do the trick. From SQL BOL:

  sp_restoredbreplication

  Removes replication settings if restoring a database to the non-originating server, database, or system that is otherwise not capable of running replication processes. When restoring a replicated database to a server or database other than the one where the backup was taken, replication settings cannot be preserved. On the restore, the server calls sp_restoredbreplication directly to automatically remove replication meta data from the restored database.

  Syntax

  sp_restoredbreplication [ @srv_orig = ] 'original_server_name'

  , [ @db_orig = ] 'original_database_name'

  [ , [ @keep_replication = ] keep_replication ]

  Arguments

  [@srv_orig =] 'original_server_name'

  The name of the server where the back up was created. original_server_name is sysname, with no default.

  [ @db_orig = ] 'original_database_name'

  The name of the database that was backed up . original_database_name is sysname, with no default.

  [@keep_replication =] keep_replication

  For internal use only.

  Remarks

  sp_restoredbreplication is used in all types of replication.

  Permissions

  Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_restoredbreplication.