We have a replicated database (transactional replication) that is primarily used for reporting, although this also could be a secondary or tertiary (3rd option) disaster recovery solution if the other DR options fail. I inherited this situation, and it is not in ideal shape. A fair amount of the metadata has not been replicated from the production database, and it has proven to be more of a task than I thought to try to get this corrected. So, it would seem a good idea to just restore the replicated database from backup and then re-initiate replication. However, I am unsure of some things which will play into how I need to implement this.
We are using SQL Server 2005. The size of the database is over 8GB.
The full backup of the production database occurs shortly after midnight. The replicated database is at another site. It takes a couple of hours to get the backup compressed and moved across the wire to the server where the target instance resides. So, I'm unsure of how to deal with what happens during time it takes to get the backup file to the replicated server. We do take transaction log backups, so I assume I'll also need to get these transaction logs across the wire and restored as well while replication is down. I guess my question is at some point, a "last" transaction log needs to be taken, moved across the wire, restored and replication needs to be re-initiated before another database transaction occurs after this "last" transaction log was taken. I assume I'll need to take the production database offline briefly or perform the snapshot immediately upon completion of the transaction log in question.
From all I've read, I know this is not an uncommon scenario, so is there an article or something I have missed that deals with the particular issues that I have concerns about? Perhaps this is not as complicated as I'm thinking it is. It really should be no different than initially setting up replication (there will always be at least a little time lag, right?), actually, but I just don't recall any articles addressing dealing with the time lag in setting up the replicated database (subscriber).
Give me your thoughts and maybe you can fill in the gaps I have with my understanding.