Microsoft KB Archive/171913: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 115: | Line 115: | ||
** Disallow even DBOs from executing this procedure. | ** Disallow even DBOs from executing this procedure. | ||
*/ | */ | ||
if suser_id() < | if suser_id() <> 1 | ||
begin | begin | ||
raiserror(15003,-1,-1) | raiserror(15003,-1,-1) | ||
Line 124: | Line 124: | ||
** Must execute from master database | ** Must execute from master database | ||
*/ | */ | ||
if db_id() < | if db_id() <> 1 | ||
begin | begin | ||
raiserror(5001,-1,-1) | raiserror(5001,-1,-1) | ||
Line 136: | Line 136: | ||
begin | begin | ||
print 'Usage:' | print 'Usage:' | ||
print 'sp_MSunpublish_db <dbname | print 'sp_MSunpublish_db <dbname>' | ||
return (1) | return (1) | ||
end | end | ||
Line 150: | Line 150: | ||
** If @dbname not found, say so and list the databases | ** If @dbname not found, say so and list the databases | ||
** other than master, tempdb, model and msdb that are not published. | ** other than master, tempdb, model and msdb that are not published. | ||
** if server upgraded from 4.2x, msdb may have dbid | ** if server upgraded from 4.2x, msdb may have dbid > 4 | ||
*/ | */ | ||
if @dbid is null | if @dbid is null | ||
Line 157: | Line 157: | ||
print '' | print '' | ||
select 'Available databases:' = name | select 'Available databases:' = name | ||
from sysdatabases where dbid | from sysdatabases where dbid > 3 and ((category & 1) <> 1) and (name <> 'msdb') | ||
return (1) | return (1) | ||
end | end | ||
Line 175: | Line 175: | ||
** If within a transaction, disallow | ** If within a transaction, disallow | ||
*/ | */ | ||
if (@@trancount | if (@@trancount > 0) | ||
begin | begin | ||
raiserror (15002,-1,-1,@procname) | raiserror (15002,-1,-1,@procname) | ||
Line 197: | Line 197: | ||
/* mark database as published for sp_repldone */ | /* mark database as published for sp_repldone */ | ||
update sysdatabases set category=category | 1 where dbid=@dbid | update sysdatabases set category=category | 1 where dbid=@dbid | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error updating master..sysdatabases' | print 'Error updating master..sysdatabases' | ||
Line 206: | Line 206: | ||
/* remove filter procedures */ | /* remove filter procedures */ | ||
EXEC ("USE "+ @dbname + " delete syscomments from syscomments,sysobjects where syscomments.id=sysobjects.id and sysobjects.type='RF'") | EXEC ("USE "+ @dbname + " delete syscomments from syscomments,sysobjects where syscomments.id=sysobjects.id and sysobjects.type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 215: | Line 215: | ||
EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysobjects where sysprocedures.id=sysobjects.id and sysobjects.type='RF'") | EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysobjects where sysprocedures.id=sysobjects.id and sysobjects.type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 224: | Line 224: | ||
EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysobjects where sysprotects.id=sysobjects.id and sysobjects.type='RF'") | EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysobjects where sysprotects.id=sysobjects.id and sysobjects.type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 234: | Line 234: | ||
EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysobjects where sysdepends.id=sysobjects.id and sysobjects.type='RF'") | EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysobjects where sysdepends.id=sysobjects.id and sysobjects.type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 243: | Line 243: | ||
EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysobjects where syscolumns.id=sysobjects.id and sysobjects.type='RF'") | EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysobjects where syscolumns.id=sysobjects.id and sysobjects.type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 252: | Line 252: | ||
EXEC ("USE "+ @dbname + " delete from sysobjects where type='RF'") | EXEC ("USE "+ @dbname + " delete from sysobjects where type='RF'") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 262: | Line 262: | ||
/* remove Views used as sync objects */ | /* 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") | EXEC ("USE "+ @dbname + " delete syscomments from syscomments,sysarticles where sysarticles.objid != sysarticles.sync_objid and syscomments.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 271: | Line 271: | ||
EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprocedures.id= sysarticles.sync_objid") | EXEC ("USE "+ @dbname + " delete sysprocedures from sysprocedures,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprocedures.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 280: | Line 280: | ||
EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprotects.id= sysarticles.sync_objid") | EXEC ("USE "+ @dbname + " delete sysprotects from sysprotects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysprotects.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 289: | Line 289: | ||
EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysdepends.id= sysarticles.sync_objid") | EXEC ("USE "+ @dbname + " delete sysdepends from sysdepends,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysdepends.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 298: | Line 298: | ||
EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysarticles where sysarticles.objid != sysarticles.sync_objid and syscolumns.id= sysarticles.sync_objid") | EXEC ("USE "+ @dbname + " delete syscolumns from syscolumns,sysarticles where sysarticles.objid != sysarticles.sync_objid and syscolumns.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 307: | Line 307: | ||
EXEC ("USE "+ @dbname + " delete sysobjects from sysobjects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysobjects.id= sysarticles.sync_objid") | EXEC ("USE "+ @dbname + " delete sysobjects from sysobjects,sysarticles where sysarticles.objid != sysarticles.sync_objid and sysobjects.id= sysarticles.sync_objid") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 317: | Line 317: | ||
/* remove article info */ | /* remove article info */ | ||
EXEC ("USE "+ @dbname + " delete from sysarticles ") | EXEC ("USE "+ @dbname + " delete from sysarticles ") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 327: | Line 327: | ||
/* remove publication info */ | /* remove publication info */ | ||
EXEC ("USE "+ @dbname + " delete from syspublications") | EXEC ("USE "+ @dbname + " delete from syspublications") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 337: | Line 337: | ||
/* remove subscription info */ | /* remove subscription info */ | ||
EXEC ("USE "+ @dbname + " delete from syssubscriptions") | EXEC ("USE "+ @dbname + " delete from syssubscriptions") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 347: | Line 347: | ||
/* sysobjects.category: bit 5 : published, bit 6: subscribed -- decimal total: 96 */ | /* sysobjects.category: bit 5 : published, bit 6: subscribed -- decimal total: 96 */ | ||
EXEC ("USE "+ @dbname + " update sysobjects set category=category & ~96") | EXEC ("USE "+ @dbname + " update sysobjects set category=category & ~96") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 357: | Line 357: | ||
/* syscolumns.status: bit 4: published text col, bit 5: non-sql subscriber -- decimal total: 48 */ | /* syscolumns.status: bit 4: published text col, bit 5: non-sql subscriber -- decimal total: 48 */ | ||
EXEC ("USE "+ @dbname + " update syscolumns set status = status & ~48") | EXEC ("USE "+ @dbname + " update syscolumns set status = status & ~48") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error: ' | print 'Error: ' | ||
Line 370: | Line 370: | ||
/* Now run sp_repldone to mark any replicated tran in log as done */ | /* 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") | EXEC ("USE " + @dbname + " exec sp_repldone 0,0,NULL,0,0,1") | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
rollback transaction tran_unpublish_db | rollback transaction tran_unpublish_db | ||
Line 383: | Line 383: | ||
/* reset category as "not published" */ | /* reset category as "not published" */ | ||
update sysdatabases set category = category & ~1 where dbid=@dbid | update sysdatabases set category = category & ~1 where dbid=@dbid | ||
if (@@error < | if (@@error <> 0) | ||
begin | begin | ||
print 'Error updating master..sysdatabases' | print 'Error updating master..sysdatabases' |
Revision as of 20:44, 20 July 2020
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 */ 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.
Additional query words: sqlunpub exe utility unpublish repl uninstall load dump sql65 sql6 sqlunpub.exe publisher published
Keywords: kbinfo kbfile KB171913