sql server replication: Hi,
I have both my server at different location with around 4GB of database and
network is poor.
I am trying this feature of SQl 2005 replication which allows initial
snapshot from backup.
I could do it with pubs db but couldn't understand some points. Expert's
advice will be greatly appreciated.

To my understanding , the initial snapshot flows from publisher to
subscriber (corect me if i am wrong). When I create the subscription I have
to add those 3 valuws to the sp_addSubscription :
@sync_type = initialize with backup.
@backupdevicetype = disk
@backupdevicename = path of the backup file(s).
Why this "@backupdevicename" has to be a path on server side and not
subscriber?

If neone could guide me to an article or link where this is nicely
explained, it'll be great.

I am using 'Replication' for the fisrt time, so the question could be stupid.

SQL BOL is a good source of information for the "initialize with backup"
feature so I would encourage you to check that out. To answer your questions
(I think):

1) If you choose to use "initialize with backup", you would need to manually
restore the backup at the subscriber as the means of transferring the
initial data set. It is absolutely important that you restore the same
backup as the one you specified in sp_addsubscription. (Note: it is not
necessary to run the snapshot agent as snapshot processing will be
completely bypassed for an "initialize with backup" subscription)
2) With "initialize with backup", sp_addsubscription internally calls
RESTORE HEADERONLY to extract the last LSN of backup to be used as the
starting point of the new subscription. That is why the path that you
specified must be relative to the publisher SQL Server service as would be
the case if you execute RESTORE HEADERONLY yourself. (Not entirely sure if
that really clarifies things for you...)

It is quite unfortunate that many of us (not me personally even though I
implemented the feature) consider "initialize with backup" to be an advanced
feature internally hence very little effort was spent to shield the end user
from the complexities involved (I kind of lied here, you can have an easier
time setting this up through the P2P wizard even if you don't need the full
generality of P2P.) As such, you may need some level of understanding of how
the feature work internally and\or how things used to be (ala
http://support.microsoft.com/default.aspx?scid=kb;en-us;320499) to use the
feature effectively. Here is a list of things that you may want to watch out
for:

1) At the risk of being redundant, you need to restore the same backup (no
stopat) with recovery as the one you specified in sp_addsubscription at the
subscriber
2) Since the publisher log\distribution database may not have the replicated
transactions needed for synchronizing from a given backup (think really old
backup and or aggressive distribution cleanup agent), you may see an error
to that effect when you are trying to set up a new subscription. Resist the
urge to feel that the world is crashing down on you when you see that and
send out panic plead pleas for help to the newsgroups or forums (in other
words, don't panic:) as you can either resolve the "problem" with a newer
log\differential backup and\or temporarily stopping the distribution cleanup
agent (or simply sql server agent).
3) If you set up your publication\article with some elaborate options
(filtering e.g.), you may need to manually "scrub" the subscriber database
once it is restored so the content of the subscriber database conforms to
what the publication settings suggest.

-Raymond

[quoted text, click to view]

Thanks for the reply Raymond.

[quoted text, click to view]

Yes I agree, and I could do my first replication only after reading BOL.
My problem was , it just gives us the idea of how to do things, whereas I
needed to know 'how it happens behind curtains' .

[quoted text, click to view]

In my case the log reader agent didn't start untill I ran the snapshot agent.
Snapshot agent didn't actually created snapshot, it gave me this message.
“[0%] A snapshot was not generated because no subscriptions needed
initialization.

[quoted text, click to view]

OK, getting is somewhat.

[quoted text, click to view]

let me see this link and i'll come back to u. : )

[quoted text, click to view]

Thanks again Raymond.