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

restoring replicated database

Hi,
I've restored a merge replicated database (publisher) on a new server
without replication.
Now i observe that there are some default replication procedures named
*_pal in my restored database. What is the way of deleting these
replication procedures. What will be the impact of the same.
Regds,
amit
Amit,
these procedures will need cleaning up by hand. sp_removedbreplication will
remove many system objects but these procs sometimes remain. You can
hand-craft a script to delete them (using information_schema.routines) or
just do it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul thanks for you reply. We have configured this restored database
for replication and it has created new *pal. procedures. Is it ok if we
delete the earlier *pal procedure now?
|||Yes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks, but can you just let me know if there would be any impact if i
keep the old procedures instead of deleting them as i would not like to
change the system configuration if there is not impact.
|||If you mean problems caused by name conflicts and the like, I think the
complexity of the name ensures this isn't the case. I have seen systems that
have accumulated orphaned procs like this over several years and the DBAs
didn't even notice they were there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql