If I have SQL Log Shipping scheduled as follows:
Primary Server (VM): Backup Job Schedule – Every 5 Minutes (To LogCopyTo)
Secondary Server (VM): Copy Job – Every 15 Minutes (To LogCopyFrom)
Secondary: Restore Job – Every 25 Minutes No Recovery Mode
If I take a snapshot of the Secondary VM remove it from the network (for 24 hours) and run "Restore With Recovery" on each database and work with the data for testing purposes and restore the snapshot and put it back on the network, will log shipping catch up and continue without issues?
Best Answer
Your best bet is to test it out. You did not told us what would you do to the transaction log backup job on primary will it keep on happening as per schedule?, if so do you have enough space on drive holding transaction log backups ? Also for this exercise it would be better to have secondary database in standby mode before you recover it
If you do not disable the backup job then you have to make sure you keep all the log files intact and after you add the secondary back the mammoth task of restoring all those files would be on restore job and it will sure fire some alerts that log shipping is out of sync but since log chain is not disturbed it should eventually catch up.
Now other scenario can be you disable all the log shipping jobs and is ready to run primary database exposed (which would not be correct thing, its your call) and now you go ahead and remove secondary from log shipping for 24 hours. When you will add it back, subject to condition you can do it successfully, the log shipping should work. The reason I say it should work is I do not see any log chain which is disturbed or broken and after adding the secondary server. The challenge here also is whether you are successfully able to restore the snapshot, please note I have seen many a times this thing failing that is why i stressed on testing it.