July 27, 2009 at 10:13 am
Hi,
I have log shipping setup and we use the standby database as a reporting read_only db. I ship logs every 15 minutes and then at 1am, the restore job kicks off and updates the standby database. Last night it was interrupted with this error:
2009-07-26 02:41:43.93*** Error: [highlight=#ffff11]Could not apply log backup [/highlight]file 'I:\Shipping_TransLogs\MVXPRD_20090725230001.trn' to secondary database 'MVXPRD_STANDBY'.(Microsoft.SqlServer.Management.LogShipping) ***
2009-07-26 02:41:43.93[highlight=#ffff11]*** Error: The media family on device 'I:\Shipping_TransLogs\MVXPRD_20090725230001.trn' is incorrectly formed. SQL Server cannot process this media family.[/highlight]
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
So now my standby database is stuck in restoring mode. I have tried the RESTORE DATABASE 'DB_NAME' WITH RECOVERY just to get the database back up so the users can do their reporting and then later tonight restart the whole process by restoring the latest backup of production and start the log shipping again.
The restore of our production db takes about 5 hours so my goal right now is that I just want to get the database back into READ_ONLY or WITH RECOVERY so that the users can do their work. Here is the error I get when I tried to RESTORE WITH RECOVERY:
A previous restore operation was interrupted and did not complete processing on file 'TMVXTRANL01'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Is the only way to fix this to start all over and restore a backup from my production?
Thanks!
Isabelle
Thanks!
Bea Isabelle
July 27, 2009 at 10:19 am
Have you tried putting a WITH REPLACE in the RESTORE statement?
July 27, 2009 at 10:59 am
Hi,
But wouldn't WITH REPLACE just be restoring the database from a backup of production and it will just restore over the existing one? To do a complete restore from a backup will take at least 5 hours so I know to get the log shipping back up I will need to do that, but what I'm looking for right now is how to make the standby database available to our users for reporting without having to do a complete restore. How can I get it out of 'restoring' mode without doing a complete restore?
Thanks,
Isabelle
Thanks!
Bea Isabelle
July 29, 2009 at 8:58 am
Hi Isaballe,
Based on the error that you have mentioned, one of the transaction log backup might have got corrupted in between while transaction log backup is happening could be due to an IO issue.
When log shipping restore job started applying corrupted transaction log backup file, it started restoring from the start point and then failed at the point where there could be a corruption.
Can you verify by checking whether the transaction log backup file that is creating issue is valid or not?
If the log backup file is not valid, then the only option left is to restore full database again and there is no possiblity to bring database in standby mode as log backup file is corrupted.
The other option you can try is by restoring the same tlog backu file by making use of stopat option.
I don't know whether this will work or not but you can always give a try.
Murthy
August 7, 2009 at 5:56 pm
Hi Murthy,
One of the transaction logs was corrupted. I ran the RESTORE VERIFYONLY on the last log that it was trying to restore and I got a message that it was unusable. So I ended up refreshing the standby with a full backup of the primary db and start the log shipping from there.
Thanks for the reply! 😀
Bea
Thanks!
Bea Isabelle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply