Restoring Replicated DB to NonReplicated Server

Hi all. I am working on a SQL 7.0-2000 upgrade testing/planning
scenario. My question is: Is there any way to restore a backup of a
published DB to a non-replicated server?
When we go live, I plan to drop all of the subscriptions and
publications before restoring the databases onto the new SQL 2000 SP4
servers, but for testing, I want to take a backup of the live
replicated database and restore it onto a test server without any trace
of replication. Alternatively, is there an easy way to remove all
traces of replication (i.e. replication tables/triggers/etc.) once I
have restored the backup of the replicated DB onto the test server?
Any help/advice is greatly appreciated.
Thanks,
Peter
Peter,
most remaining replication objects can be removed using
sp_removedbreplication. The conflict tables will be left hanging and a few
views, but it should be 99% clean after this.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this get rid of the replication triggers as well? That is what I
am most concerned about. Also, not sure if it makes any difference, but
I am using merge replication.
Thanks,
Peter
Paul Ibison wrote:
> Peter,
> most remaining replication objects can be removed using
> sp_removedbreplication. The conflict tables will be left hanging and a few
> views, but it should be 99% clean after this.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||I tried to restore the SQL 7.0 replicated database onto SQL 2000 SP4,
but the restore fails with a "Invalid column name 'excluded_cols'."
error.

>From what I found in a quick Google search, it sounds like this is
probably due to replication. My question is: Is the database fully
restored at this point and I can just run sp_removedbreplication? Or
will I have to find space on a SQL 7.0 server to restore the replicated
database, then run sp_removedbreplication, then back up that database,
then restore it to SQL 2000?
Thanks,
Peter
|||Peter - yes it'll remove the replication triggers.
Cheers,
Paul Ibison
|||Is the database visible in the sql server 2000 instance? If it is,
sp_removedbreplication should do the trick. If not, restore the backup on
the SQL Server 7.0 instance as a different name, remove replication from
this database, back it up then restore on the sql 2000 instance.
HTH,
Paul Ibison