Monday, March 12, 2012

Restore published database to test server without replication?

What is the correct way to restore a backup of a published database onto a
different server with no replication? Here's my situation. The production
db (Sql 2k sp3a) is published using transactional replication and a remote
distributor. I restored a full backup of this database onto a test server
without replication. But I can't truncate the t-log because there are
un-replicated transactions, which can be seen with DBCC OPENTRAN. I tried to
execute sp_repldone NULL, NULL, 0, 0, 1 but get the message that the db is
not published. Sp_removedbreplication executes, but I still have the same
problems. I finally enabled the test server/db as a publisher, ran
sp_repldone, and then removed replication.
Is there a better way to do this?
Donna,
you seem to have done all the correct things. I'm not too sure that it is a
better way, but I have got round this type of issue by detaching the
database, renaming the log then reattaching, and a new (empty) log has been
created. This is obviously only any good if you can afford to lose the
transaction log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

No comments:

Post a Comment