April 18, 2010 at 12:34 pm
Hi,
We have SQL Server 2005 EE x64 with SP3. We have configured log shipping for the database Mydb. Log shipping working fine from last 6 months. But yesturday onwards, the restore job on Secondary server is failing and when I looked at the error log, I found the below error.
The backup data at the end of "\\SQLDR\log_backups\Mydb_PROD_20100414050000.trn" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
I did the following on DR Server:
Use master
go
RESTORE HEADERONLY FROM DISK='\\SQLDR\log_backups\Mydb_PROD_20100413060000.trn'
Results:
*** INCOMPLETE ***NULLNULLNULLNULL1NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
From the above results, I did not understanding what happened the log file "Mydb_PROD_20100413060000.trn"
1. Do I need to take full backup of primary and restore to secondary with NORECOVERY & Configure log shipping again? or
2. Can I fix the error without reconfiguring the Log shipping from scratch?
Please advice me what steps I should take now
Thanks
April 18, 2010 at 1:26 pm
you will need to do a full restore with norecovery again but you wont have to reconfigure logshipping, Just temporarily disable the log shipping jobs, move log backups from BEFORE your full backup out of the logshipping share on the primary, and delete them from the secondary.
Then do a manual run of the log shipping jobs (backup,copy, restore), once successful then reenable the log shipping jobs again and continue on as before.
---------------------------------------------------------------------
April 18, 2010 at 1:31 pm
Thanks George,
But I'm more interested to know what caused the restore failure and prevent it to happen again. Because it's a big database of size 50 GB and it takes a day to copy the backup of the database to secondary?
If it is a IO error, then I need to prove that to Systems Admin and ask to verify that all the drives are healthy and has NO corruption
If it is our (DBA) issue, then I wantto avoid this type failure to happen again
Thanks for help
April 18, 2010 at 2:09 pm
If you have that log backup on primary and secondary, check it out on the primary as well.
If its ok on the primary it was corrupted by the copy so you had a network glitch. (you could try copying that log again manually and see if it will restore, SQL will have it as the next log to restore).
Else most likely an io error, not SQL, especially if no errors in SQL Check eventvwr and get sysadmins to check disks out. you should not have to try and prove to them its not SQL, they should take a potential disk problem seriously and check out their drives.
---------------------------------------------------------------------
April 18, 2010 at 2:36 pm
If you have that log backup on primary and secondary, check it out on the primary as well.
If its ok on the primary it was corrupted by the copy so you had a network glitch. (you could try copying that log again manually and see if it will restore, SQL will have it as the next log to restore).
Else most likely an io error, not SQL, especially if no errors in SQL Check eventvwr and get sysadmins to check disks out. you should not have to try and prove to them its not SQL, they should take a potential disk problem seriously and check out their drives
I do NOT have that log file on Primary but I did have in secondary. I just tried to copy that log file from secondary to primary, and I'm able to copy without any errors. I have checked event logs,error log on Primary server and I did NOT see any errors.
Are there any other places I can check to get more details on Primary or Secondary?
thanks
April 18, 2010 at 8:55 pm
I did come across the same issue but I did not find the cause for restore failure. I appreciate if you could you provide more details, if you find the reason!
April 19, 2010 at 3:40 am
pshaship (4/18/2010)
If you have that log backup on primary and secondary, check it out on the primary as well.
If its ok on the primary it was corrupted by the copy so you had a network glitch. (you could try copying that log again manually and see if it will restore, SQL will have it as the next log to restore).
Else most likely an io error, not SQL, especially if no errors in SQL Check eventvwr and get sysadmins to check disks out. you should not have to try and prove to them its not SQL, they should take a potential disk problem seriously and check out their drives
I do NOT have that log file on Primary but I did have in secondary. I just tried to copy that log file from secondary to primary, and I'm able to copy without any errors. I have checked event logs,error log on Primary server and I did NOT see any errors.
Are there any other places I can check to get more details on Primary or Secondary?
thanks
being able to copy it does not prove either way what happened to the file on the original copy. If you no longer have the file on the primary you cannot prove anything.
You may have to put this one down to experience if no errors were reported. I still its worth sysadmins checking the disk you back up to.
---------------------------------------------------------------------
April 19, 2010 at 4:41 pm
The disk I backup the databases is on Primary and I did NOT see any errors on primary when this restore is failed on secondary. Everything seems fine on backup drive on primary. I'm suspecting the backup drive on secondary (where the log shipping backups copied to). Is that makes sense to ask sysadmin to check the backup drive on Secondary?
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply