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
