January 26, 2010 at 8:52 am
Hi
I am using SQl 2005 Ent Edition & transactional replication.
Publisher , Subscriber & distributor are on the same SQL server.
The Subscriber database is only used for reporting purpose.
Every night I run a full backup of the Publisher database.
How can I restore a 1 day old backup of the Publisher database ( of course I will loos 1 day of data but that is acceptable) without delete/create replication ?
So far I
1) stop the log reader,
2) restore the Publisher db
3) start the log reader
but of course after that replication failed
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a8afcdbc-55db-4916-a219-19454f561f9e.htm
one more important information I do not have have a Subscriber backup
January 28, 2010 at 12:52 pm
Are you restoring the back up with Keep replication option?
Even if you are I think you will still fail since the publisher and subscriber DB are out of Synch. You will have a fresher copy of the DB in the subscriber than publisher.
-Roy
January 28, 2010 at 2:51 pm
I am too testing Transactional Replication Backup / Restore. What are the other options?
Scenario 1:
My publisher DB is running but for some reason i need to restore.
In this scenario, i can drop the subscriber, drop the publication,
Restore the Publisher from backup (Delete extra loaded rows from subscriber)
=> Create new publication , Create new subscription
Scenario 2:
My publisher DB crashed:
In this case i don't think it is possible to drop the subscription/publication.
Hence restore the publication db keeping the replication (The subscriber would
be out of sync with publication at this stage). Hopefully it should allow me to
drop the subscription followed by dropping of the publication , delete the
redundant data at the Subscribe DB and then create pub/subscription.
Hopefully this should work.
Feel free to correct me.
January 29, 2010 at 6:53 am
because I do not have a Subscriber backup, when restoring my Publisher database I first delete replication settings
exec sp_removedbreplication
-- Dropping the registered subscriber
exec sp_dropsubscriber @subscriber = N'SRV'
-- Dropping the distribution publishers
exec sp_dropdistpublisher @publisher = N'SRV'
-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @database = N'distribution'
GO
/****** Uninstalling the server as a Distributor. ******/
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
restore Publisher db and re-create replication (using script previously generated with Management Studio )
this is the ony solution I've got
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply