SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


full database restore of the Publisher


full database restore of the Publisher

Author
Message
egpotus
egpotus
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 1278
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


Kindest Regards,

egpotus DBA

Roy Ernest
Roy Ernest
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10933 Visits: 6892
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
gk-411903
gk-411903
SSC Eights!
SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)

Group: General Forum Members
Points: 999 Visits: 420
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.
egpotus
egpotus
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 1278
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


Kindest Regards,

egpotus DBA

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search