Hi all, I had the issue twice back to June, and Oct this year.
Our environment uses log shipping between two long distance geographical locations. One day on June(another on Oct), the secondary db restore job was failed, the secondary db was on SUSPECT mode.
As searched from forum, tried to set the db to emergency mode, but error message indicates the action failed because the db was still in restore process.
I have never heard of a secondary DB going from Standby/Readonly
into Suspect mode. That is an interesting scenario. You would need to recover the database before you could try changing to the Emergency state and if it is Suspect I am not sure you could even accomplish that. Did you try? Even if you could, recovering the database would break log shipping so it would be of no help to you.
Since the secondary db is designed as stand by read only mode, am I right that the db couldn't be backup?
Correct. You cannot backup databases in Standby/Readonly
So that on secondary db location there won't be any full backup set to recovery.
What had i done was:
1. stop log shipping from primary db;
2. started a new full backup on primary db;
3. build a maintenance plan for primary db log backup;
4. copy the full backup and backup logs from primary db location to secondary db location(where i work);
5. restore full backup and the backup logs in stand by mode;
6. restart a new log shipping job on primary db.
My question: is this would be best way to deal with the issue, is there any other way could be resolving the issue faster, my primary db is 120GB, copy full backup(even zipped) over through network will take 15+ hours, and my secondary db need to be accessible according to our business requirement.
Given what you have said I think rebuilding log shipping from a new backup of the primary database is your only option and the steps you outlined look fine to me.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato