A previous restore operation was interrupted and did not complete processing on file
Question
Thursday, September 6, 2007 5:30 PM
Hi
I am new to Forum. So not sure if i am posting my problem uner the right topic.
We have a sql server 2005 enterprise edition 4 way cluster on windows 2003 advance server.
I am logshipping these database to a different server at a different location.
My logshipping went fine until one the cluster server failed and the server instance failed over to another node.
The backup that happened around that time got copied over to the secondary by the copy job.
The log file that got copied to the secondary server tried restoring and i think it failed int he middle of restoring it.
(You would think that the sql would knoe if the backup is in complete and will move on to the next file. Not sure what happened there.)
There is no indication of the *.TUF file in the directory where i have the log files.
I tried restoring it manually and i got the following error
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I looked in the msdb..log_shipping_secondary_databases and looked for the last file that it restored and tried restoring it again with the following restore command by removing and adding some of the keywords that you see after the "WITH" clause.
MSFT do not recommand to use continne_after_error unless its absolutley necessary. I stilll get the above error.
restore log sessiondata
from disk = 'I:\sql13qasm\logs\sessiondata\sessiondata_20070901124516.trn'
with restart, CONTINUE_AFTER_ERROR, norecovery
When i add the restart int he with clause,
The restart-checkpoint file 'J:\Microsoft SQL Server\MSSQL.5\MSSQL\Backup\sessiondata.CKP' was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.
Msg 4319, Level 16, State 1, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I checked it the backup directory and i can't locate the .CKP file.
Does anyone ever come accross this issue?
Is there anyother way i could recover this DB in a standby or norecovery mode.
Any kind of help to resolve this issue (beside copy the full backup and redo the whole log-shipping process again) would be appreciated. sicne my primary and secondary server are totally ina different location, i need to ship a tape, if i need a full backup. This is the 3rd time its happening on that cluster. its frustrating to ship a tape everytime this happens.
All replies (4)
Thursday, September 6, 2007 6:45 PM
I think the log backup chain has been broken. Try doing a differential and log backup and restoring those on the secondary. If that doesn't work I think you'll need to go the full backup route again.
I'm not an expert on log shipping though - maybe someone else can chime in here?
Thursday, September 6, 2007 7:10 PM
I can't take a differential now, since full back job happened already, after the failure.
:-S
Monday, September 17, 2012 11:09 AM | 2 votes
For future reference, I manged to safe the log shipping process by executing the following process:
1) Get the transaction log backup file names of both just before and just after the file reporting the restore failure.
2) Stop all transaction log ship restore job/s (disable), applicable to the failed database/s.
3) Execute the following command with using the transaction log backup file before the corrupt file
restore log {database name}
from disk = '{path}\backup file name 1}'
with continue_after_error, norecovery;
4) Verify if the failed file is usable (just to confirm that it is corrupt)
restore verifyonly
from disk = '{path}\backup file name 2}';
5) Then manually restore the next file in the sequence
restore log {database name}
from disk = '{path}\backup file name 3}'
with norecovery
6) Now update all msdb log shipping tables to reflect the last restored file as {backup file name 3}
-- on the monitor database instance, if different to the the secondary database instance
begin transaction
update [msdb].[dbo].[log_shipping_monitor_secondary]
set last_restored_file = '{path}\backup file name 3}'
where [secondary_database] = '{database name}'
commit
-- on the secondary (standby) database instance
begin transaction
update [msdb].[dbo].[log_shipping_secondary_databases]
set last_restored_file = '{path}\backup file name 3}'
where [secondary_database] = '{database name}'
commit
begin transaction
update [msdb].[dbo].[log_shipping_monitor_secondary]
set last_restored_file = '{path}\backup file name 3}'
where [secondary_database] = '{database name}'
commit
7) Now you can restart the log shipping restore job and verify that the restore processes did recover.
Note: If the backup file is corrupt due to a disk error and the transaction log backup was marked as complete with a truncate succeeding on the primary instance, this method will not work as it is based on a {backup file name 3} containing all transactions of the "corrupt" file, {backup file name 2}. In this case, only a full backup and resetting the log shipping process with same will work.
8) Remember to re-enable the restore job/s.
Friday, September 21, 2012 3:15 PM
Hello Uday,
I think you are hitting a known Issue. Do dbcc dbrecover('sessiondata')) before you start restoring additional TLOGS. It might fix the issue.
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog Space |Twitter
Please click the **Mark as answer **button if this reply solves your problem.