December 10, 2012 at 12:03 pm
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.
Since the secondary db is designed as stand by read only mode, am I right that the db couldn't be backup? 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.
I hope someone could answer my question.
thanks
December 11, 2012 at 4:24 pm
yi.hua72 (12/10/2012)
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
December 11, 2012 at 4:35 pm
PS Did you ever figure out why the database went to Suspect? Was there anything of interest in the SQL Server Error Log?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 12, 2012 at 10:51 am
thanks a lot! opc.three,
the very last message on sql log for that database is "The database <db_name> is marked RESTORING and is in a state that does not allow recovery to be run."
I checked the error log, didn't found any information related the time when the "suspect" happening.
even more went into physical sql box to verify the event log, had nothing found neither.
My guess it could be some hardware issues(not failure)
If the issue would have stroke back, i will post all log info, see if i would missed something on previous verification.
Thanks again for your reply.
April 18, 2013 at 1:50 am
HI
even i have faced this complicated issues after dc dr drill
[switch over and switch back ] secondary database is corrupted and gone on into suspect mode uanble to recover
steps i followed
1. primary database size is 100gb nearly so it is difficult to copy and restore [or ] by using file transfer from unix box etc... the backup file from primary to secondary it will take so much of time and users might effected the downtime of business
2. created a device backup in primary server and divided the backup file in to 10 parts of 10Gb [total 100gb and more ]
3.created a maintenance plan job for present and future use also and executed the device backup maintenance plan.. after completing your backup disable the JOB
3. collect all divided backup files and try to copy one by one from primary to secondary location which might not cause network break for transferring the file [if file size is large it might cause network link fail ]
4. delete old database and restore the backup files in the secondary with stand-by/read-only mode and try to synchronize the logshipping database
Thanks
Naga.Rohitkumar
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply