INF: SQL Server 6.5 Stored Procedure To Remove Replication From Restored Database (171913)

MORE INFORMATION

1. Open an isql_w session and connect to the restored database.
2. Cut and paste the entire CREATE PROCEDURE T-SQL script in this article to the isql_w query window.
   
3. Run the script. The script creates the sp_MSunpublish_db stored procedure. Verify that there are no errors in the output from the script.

   The sp_MSunpublish_db stored procedure removes publication and subscription information from the database. Additionally, the stored procedure marks published objects as unpublished and removes any transactions marked for replication from the transaction log.

4. Run the sp_MSunpublish_db stored procedure with the database name as the first parameter. For example:
   

   EXEC sp_MSunpublish_db pubs
   						

CREATE PROCEDURE T-SQL Script

use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
drop proc sp_MSunpublish_db
go

CREATE PROCEDURE sp_MSunpublish_db @dbname varchar(30) 
AS
/*  
**  Purpose: 	to remove all replication related info from a database that
**  		has been loaded from a database dump of a published database. This
**		will allow this database to be used as a backup.
**
**  Security:	can be executed ONLY by SA.
**
**  Works on:	any user database (excludes master, model, msdb and tempdb).
**
**  Tables:	master..sysdatabases -- category 
**		@dbname: syscomments, sysprocedures, syscolumns, sysobjects: delete
**				filter stored procedures and views 
**		@dbname: sysarticles, syspublications, syssubscriptions: delete *
**		@dbname: sysobjects.category,  syscolumns.status : update
**		DBTABLE for @dbname -- dbt_replxrid, dbt_distxrid
*/ 

set nocount on

/* 
** Declarations
*/ 

declare @dbid int
declare @procname varchar(30)

/* 
**  initialize
*/ 
select @procname='sp_MSunpublish_db'

/*
**  Only the SA can execute this procedure -- so check.
**  Disallow even DBOs from executing this procedure.
*/ 
if suser_id() <> 1
begin
	raiserror(15003,-1,-1)
	return (1)
end

/* 
** Must execute from master database
*/ 
if db_id() <> 1
begin
	raiserror(5001,-1,-1)
	return (1)
end

/*
**  print usage if null dbname
*/ 
if (@dbname IS NULL)
begin
	print 'Usage:'
	print 'sp_MSunpublish_db <dbname>'
	return (1)
end

/*
**  Verify the database name and save info
*/ 
select @dbid = dbid
	from sysdatabases
	where name = @dbname

/*
**  If @dbname not found, say so and list the databases 
**  other than master, tempdb, model and msdb that are not published.
**  if server upgraded from 4.2x, msdb may have dbid > 4
*/ 
if @dbid is null
begin
	raiserror(15010,-1,-1,@dbname)
	print ''
	select 'Available databases:' = name
		from sysdatabases where dbid > 3 and ((category & 1) <> 1) and (name <> 'msdb')
	return (1)
end

/* 
**  if attempted on master, model, msdb or tempdb error out
*/ 

if  (@dbid <= 3) or (LOWER(@dbname) = 'msdb')
begin
	print 'Error: '
	print 'Cannot run on MASTER, TEMPDB, MODEL or MSDB'
	return (1)
end

/*
** If within a transaction, disallow
*/ 
if (@@trancount > 0)
begin
	raiserror (15002,-1,-1,@procname)
	print ''
	return (1)
end

/*
**  We need to mark the database as published, to run sp_repldone. Dont use
**  sp_dboption for this since this will create a logreader task.
**  Drop all views and filter stored procs manually (syscomments, procedures and sysobjects).
**  Then, remove info from sysarticles, syspublications and syssubscriptions.
**  Then, update sysobjects to zero out category bits 5 and 6. Also, if text cols
**  are published, syscolumns.status bits 4 and 5 will be affected -- zero out.
**  Finally, run sp_repldone to mark all tran in log for repl as done.
**  do this with tran protection -- we should not have dangling info.
*/ 

begin transaction tran_unpublish_db

/* mark database as published for sp_repldone */ 
update sysdatabases set category=category | 1 where dbid=@dbid
if (@@error <> 0)
begin
	print 'Error updating master..sysdatabases'
	rollback transaction tran_unpublish_db
	return(1)
end

/* remove filter procedures */ 
EXEC ("USE "+ @dbname + " delete syscomments from syscomments,sysobjects where syscomments.id=sysobjects.id and sysobjects.type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syscomments for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysobjects where sysprocedures.id=sysobjects.id and sysobjects.type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysprocedures for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysobjects where sysprotects.id=sysobjects.id and sysobjects.type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysprotects for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end


EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysobjects where sysdepends.id=sysobjects.id and sysobjects.type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysdepends for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysobjects where syscolumns.id=sysobjects.id and sysobjects.type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syscolumns for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete from sysobjects where type='RF'")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysobjects for filter procs in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

/* remove Views used as sync objects */ 
EXEC ("USE "+ @dbname + " delete syscomments from syscomments,sysarticles where sysarticles.objid != sysarticles.sync_objid and syscomments.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syscomments for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprocedures.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysprocedures for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprotects.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysprotects for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysdepends.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysdepends for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysarticles where sysarticles.objid != sysarticles.sync_objid and syscolumns.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syscolumns for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

EXEC ("USE "+ @dbname + " delete sysobjects from sysobjects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysobjects.id= sysarticles.sync_objid")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysobjects for views in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

/* remove article info */ 
EXEC ("USE "+ @dbname + " delete from sysarticles ")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete sysarticles in ' + @dbname 
	rollback transaction tran_unpublish_db
	return (1)
end

/* remove publication info */ 
EXEC ("USE "+ @dbname + " delete from syspublications")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syspublications in ' + @dbname
	rollback transaction tran_unpublish_db
	return (1)
end

/* remove subscription info */ 
EXEC ("USE "+ @dbname + " delete from syssubscriptions")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to delete syssubscriptions in ' + @dbname
	rollback transaction tran_unpublish_db
	return (1)
end

/* sysobjects.category: bit 5 : published, bit 6: subscribed -- decimal total: 96 */ 
EXEC ("USE "+ @dbname + " update sysobjects set category=category & ~96")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to update sysobjects in ' + @dbname
	rollback transaction tran_unpublish_db
	return (1)
end

/* syscolumns.status: bit 4: published text col, bit 5: non-sql subscriber -- decimal total: 48 */ 
EXEC ("USE "+ @dbname + " update syscolumns set status = status & ~48")
if (@@error <> 0)
begin
	print 'Error: '
	select 'Unable to update syscolumns in ' + @dbname
	rollback transaction tran_unpublish_db
	return (1)
end

/* ensure we can get in as logreader */ 
EXEC ("USE "+ @dbname + " exec sp_replflush")

/* Now run sp_repldone to mark any replicated tran in log as done */ 
EXEC ("USE " + @dbname + " exec sp_repldone 0,0,NULL,0,0,1")
if (@@error <> 0)
begin
	rollback transaction tran_unpublish_db
	return(1)
end

EXEC ("USE "+ @dbname + " checkpoint" )

/* release our hold on the db as logreader */ 
EXEC ("USE "+ @dbname + " exec sp_replflush")

/* reset category as "not published" */ 
update sysdatabases set category = category & ~1 where dbid=@dbid
if (@@error <> 0)
begin
	print 'Error updating master..sysdatabases'
	rollback transaction tran_unpublish_db
	return(1)
end

/*
** if here, ok to commit
*/ 

commit transaction tran_unpublish_db

select 'Replication related information successfully removed from Database: '+@dbname

return (0)
go
sp_configure 'allow', 0
go
reconfigure with override
go
				

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.