Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/171913

From BetaArchive Wiki
Knowledge Base


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

  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.



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

Keywords: kbinfo kbfile KB171913