SQL Server - Replication Failed‏

[elizayiu]

Purpose: Move database files to a standard location

Steps:
1) Tried to detach database but failed because database is setup forreplication (publisher).
2) Generated scripts for dropping and creating replication
3) Ran the script for dropping replication (i.e. distribution databasewas removed)
4) Detached, moved, and re-attached database
5) Ran the script for creating replication (i.e. distribution databasewas re-created)
6) Recieved error because failed to login and subscription settings inthe publisher was not created by the script
7) Fixed the problem in 6) seventeen hours later. Therefore, nothingpublished or subscribed during this period of time. Databases not in-sync.

Question:
- Since the distribution database was re-created, did I lost all thedata since last subscription?
- After the problem was fixed in 6), does the publisher pickup fromthe last published transaction (i.e. seventeen hours ago)?
- How can I fix this problem? Can it re-sync by reading from thedatabase transaction log file?

Thanks,
Eliza

[rmiao]

1. you'll not lose data if it's one way replication.
2. will not since you recreated replication.
3. try recreate snapshot.

[elizayiu]

Yes, it's one way replication. And FYI, it's up and running now. Therefore, I just need to re-sync data for that 17 hours downtime. Hopefully it's possible.

Could you please give me more details on how to recreate snapshot? I'm new to replication. Or are there any useful links?

[rmiao]

Books online has details.

[elizayiu]

It'll take too long to re-create snapshot. Are there other alternatives? Is it possible to use a database comparison tool to modify the subscription database? i.e.

1) Stop the publisher's log reader and the subscriber job (i.e. disable the SQL server Agent job)
2) Use a database comparison tool to compare the databases
3) Run the scripts generated by the tool to synchronize the subscription database
4) Ensure the synchronized database works
5) Start the log reader and subscriber job

This way, there will be no down time to both the publication database and subscription database. Do you see any problems with this plan?

FYI, the articles are tables only and there are no filers or data transformation.

[rmiao]

Can try, may need mark last distributed transaction with sp_repldone.