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)

Restore published database

I need to restore published database from backup. Do I need to restore
distribution database ftom the same point in time or I have to rebuild
replication? Any sugestins are welcome.
Thanks
dk
For a merge replication you should not have to worry about it as the
subscribers will backfill the missing information.
For a transactional publication, the log reader will complain, and your best
bet is to drop your subscriptions and then resubscribe and send the snapshot
over again.
If you don't want to do this, you could resubscribe using a no sync
subscription and then do a validation to determine how out of sync you are
and cobble together a consistent database.
You could also do a sp_replrestart to get your publication going again, but
I would advise you to only do this under the guidence of a Microsoft PSS
engineer, or engineeress.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"dk" <dk@.discussions.microsoft.com> wrote in message
news:66158E1C-85B5-431E-8899-25DB6BDD3D04@.microsoft.com...
> I need to restore published database from backup. Do I need to restore
> distribution database ftom the same point in time or I have to rebuild
> replication? Any sugestins are welcome.
> Thanks
> dk
|||Thanks very much Hilary. I do have a transactional replication running, and
no initial snapshot is required, since the subscriber already has replicated
schema. I am only concirned about log reader, if it would be able to figure
out where to continue. So in your opinion, restore of the distribution
database wouldn't help eather.
"Hilary Cotter" wrote:

> For a merge replication you should not have to worry about it as the
> subscribers will backfill the missing information.
> For a transactional publication, the log reader will complain, and your best
> bet is to drop your subscriptions and then resubscribe and send the snapshot
> over again.
> If you don't want to do this, you could resubscribe using a no sync
> subscription and then do a validation to determine how out of sync you are
> and cobble together a consistent database.
> You could also do a sp_replrestart to get your publication going again, but
> I would advise you to only do this under the guidence of a Microsoft PSS
> engineer, or engineeress.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "dk" <dk@.discussions.microsoft.com> wrote in message
> news:66158E1C-85B5-431E-8899-25DB6BDD3D04@.microsoft.com...
>
>