Basically the subscriber database contains a marker in it of the last
transaction replicated to it from the distribution database.
if you restore the subscriber database and the distribution agent runs it
checks this marker and then says to the distribution database send me all
the transactions in the distribution database which occurred after this
marker.
Your problem is that the distribution clean up task will delete commands in
the distribution database which have already being applied on the
subscriber. So when you restore the database the commands the marker says
send me have long since been deleted by the distribution clean up task.
So what you need to do is either drop the subscription and recreate it, or
drop it and do a no sync, or set you minimum distribution retention to the
time period you expect to have to restore the database in (this isn't
something you can do after the fact it is something you have to plan for).
So for example if you know you might have to restore the database from
yesterday's backup your min distribution retention will be 1-2 days. This
will cause your distribution database to get larger.
You can set this setting by using the following proc:
sp_changedistributiondb 'distribution',@mindistretention=60 (the value here
is minutes).
--
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com [quoted text, click to view] "Rich" <Rich@discussions.microsoft.com> wrote in message
news:D448052A-441E-403B-996B-6E8719A07C7D@microsoft.com...
>I am new to SQL 2005 and I have a question on backup specifically restoring
>a
> database.
>
> The setup is a database that is a publisher is replicating its data to a
> sunscriber via a pull method. (Sorry if I am not using all the correct
> terms). We have SQL performing backups (Full and Differentials).
>
> In testing a restore (Subscriber), I can successfully restore the DB,.
> Howeveyr the replication part is failing. If I reinitialize replication it
> works but it is now pulling all that data across the WAN. I know I could
> use
> the Alternate snapshot method but that seems to negate using the backups
> as a
> quck revocery methood due to the fact I have to transfer the snapshot to
> the
> server which I am trying to restore.
>
> My question is:
> If I have to intialize replication is their a way to tell it to use the
> data already restored from backup and just send changes over the wire.
>
> If anyone can suggest some articles that would help me understand this I
> would appreicate it.
