Microsoft KB Archive/171913

= INF: SQL Server 6.5 Stored Procedure To Remove Replication From Restored Database =

Article ID: 171913

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q171913



SUMMARY
This article describes how you can remove replication related information from a database that has been restored from a backup (DUMP) of a published database.



MORE INFORMATION
 Open an isql_w session and connect to the restored database. Cut and paste the entire CREATE PROCEDURE T-SQL script in this article to the isql_w query window.

 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.

  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
 * @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)

/* select @procname='sp_MSunpublish_db'
 * initialize

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

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

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

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

/* 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 @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 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 (@@trancount > 0) begin raiserror (15002,-1,-1,@procname) print '' return (1) end
 * If within a transaction, disallow

/*
 * 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.

Additional query words: sqlunpub exe utility unpublish repl uninstall load dump sql65 sql6 sqlunpub.exe publisher published

Keywords: kbinfo kbfile KB171913

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.