Fixing A previous restore operation was interrupted and did not complete processing

I recently was working on a customer system where a myriad of bad things had occurred simultaneously that left them in a scenario where their primary database server had corruption of an important database and their DR Log Shipped Secondary server couldn’t be rolled forward due to the Restore job failing with the following type of error:

RESTORE LOG AdventureWorks2014
FROM DISK = ‘D:\SQLBackups\Logs\AdventureWorks2014_LOG_202001021250.trn’
WITH FILE = 1,NORECOVERY, STATS = 10

14 percent processed.
31 percent processed.
49 percent processed.
66 percent processed.

Msg 3203, Level 16, State 1, Procedure s_res_translog_for_database, Line 70
Read on “D:\SQLBackups\Logs\AdventureWorks2014_LOG_202001021250.trn” failed: 38(Reached the end of the file.)
Msg 3013, Level 16, State 1, Procedure s_res_translog_for_database, Line 70
RESTORE LOG is terminating abnormally.

Further log restores on the log shipped secondary, even manually would also get this error:

Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘AdventureWorks2014log’. 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.

Since the log shipped database was multi-terabytes in size, reinitializing it from backup wasn’t an ideal situation, but what other options are there?  The key lies in the original error message from log shipping, the file restore of the log reached the end of the file and wasn’t expecting it to be the end of the file.  This means one of two things happened:

  1. Either the log backup file was in flight when the main server crashed and restarted and only a partial backup file was written and then copied over to the log shipped secondary and the restore job tried to restore this broken file (NOTE: This was a theory that I have proven to not be possible as the header would have prevented the restore)
  2. The backup had already completed and the copy file job was running when the server crashed and the file wasn’t fully copied over to the log shipped secondary and it tried to restore it when the restore job ran, generating the first exception

Comparing the file on the source server to the file on the log shipped secondary revealed that the sizes were in fact different, which means that scenario two from above is most likely what occurred.  The backup file on the log shipped secondary was deleted and then the full file was manually copied over to the secondary server. However, when the Restore Job would run, it would still fail with error 4319. Manually applying the file with restore log would also result in:

Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘AdventureWorks2014log’. 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.

It seems totally broken, and then I tried the same file but specifying CONTINUE_AFTER_ERROR since at this point there wasn’t really much risk of doing more harm.  I was maybe 50/50 that this might work, but if it didn’t we were going to have to reinitialize from a full backup either way, so it was worth trying as it stood.

RESTORE LOG AdventureWorks2014
FROM DISK = ‘D:\SQLBackups\Logs\AdventureWorks2014_LOG_202001021250.trn’
WITH FILE = 1,NORECOVERY, STATS = 10, CONTINUE_AFTER_ERROR;

The file restored and then the Log Shipping Restore Job immediately resumed normal restore operations of the subsequent files without errors.  Since the file was not damaged, this worked and allowed the restore log operation to complete and clear the previous end of file encountered error bit successfully so further restore operations could be applied without errors.

Backup your Reporting Services Encryption Key

If you run SQL Server Reporting Services, part of your DR plan needs to include a backup of the encryption key for SSRS. This sadly is an all to often overlooked part of the solution, even though it is incredibly easy to do. If you don’t have a backup of the encryption key during a restore, the report server will never be able to decrypt the encrypted content (connection strings, passwords, etc) stored in the database, and your only recourse would be to delete the encrypted content and recreate it manually or through a redeployment of datasources.

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/ssrs-encryption-keys-back-up-and-restore-encryption-keys?view=sql-server-2017

For those that are into Powershell, Microsoft has provided a simple function Backup-RsEncryptionKey that can be utilized for this as well:

https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CAdmin%5CBackup-RsEncryptionKey.ps1

Make sure that you’ve covered all your bases with backups of other keys and certificates as well. Even if you think you’ve got your bases covered, now is a great time to verify, especially for anyone using Transparent Data Encryption for a database. One of the worst emails we’ve ever gotten from someone in the community was that they had backups of their database but not the certificate used for encryption and they couldn’t restore the database or access the data, and there is absolutely nothing anybody can do to help you if this happens.