A Previous Restore Operation Was Interrupted And Did Not Complete Processing On File

3 views

Skip to first unread message

Hayley Sweigard

unread,

Aug 3, 2024, 5:29:00 PM8/3/24

to nonsbawatro

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:

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:

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?

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.

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:

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.

This is why, in this post, we will show you what you have to do in order to restart the interrupted operation using T-SQL queries. In case your restore operation was interrupted, you can still restart the process and continue from that point where it got interrupted.

This is a feature that can be very useful if you have very large databases which you have to restore. In case the process of restoring fails close to the end, most of the time you can restart the entire operation from the point where it left off, instead of restarting the entire restoration process of the database.

To be specific, at the time when you make your restore from tape, you can restart from the current tape instead of restarting from the first one. But, if the restore was in the phase when it was being rolled forward, then no data will be copied from that backup set.

If you have used a T-SQL query to restore your database and the process was interrupted for any reason, known or unknown, then what you have to do is to specify a RESTART clause at the end of the same query and run the query once more.

One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He was restoring a large database on a Failover Cluster Production Server and while the restore was in progress, due to network failure, the restore failed. Once the SQL Server came up on the other node all the databases came up, except for the database which he was restoring prior to the failover. In this tip we will take a look at the command RESTORE DATABASE...WITH RESTART to see how this command can be helpful during such scenarios.

The RESTORE DATABASE...WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.

Restarting Interrupted Restore Operations in SQL Server. If a restore operation is interrupted, you can restart the operation from the point of interruption. This can be useful if you restore large databases.

Thanks for the tip. When you specify WITH RESTART does that mean the restore process picks up where it left off or, as the command suggests, does it restart the restore operation from the beginning? If from the beginning then what's the advantage of WITH RESTART over WITH REPLACE?

I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a "Restoring" state. I had no disk space issues-- the database simply didn't come out of the "Restoring" state.

I had a similar incident with stopping a log shipping secondary server.After the command to remove the server from log shipping and stopped the log shipping from primary server the database on secondary server got stuck in restoring status after the command

I had a similar issue with restoring using SQL Management Studio. I tried to restore a backup of the database to a new one with a different name. At first this failed and after fixing the new database's file names it was successfully performed - in any case the issue I'm describing re-occurred even if I got this right from the first time. So, after the restoration, the original database remained with a (Restoring...) next to its name. Considering the answers of the forum above (Bhusan's) I tried running in the query editor on the side the following:

which fixed the issue. I was having trouble at first because of the database name which contained special characters. I resolved this by adding double quotes around - single quotes wouldn't work giving an "Incorrect syntax near ..." error.

OK, I have similar problem and exactly as it was in case of Pauk, it was caused by the server running out of disk space while restoring and so caused a permanent restoring state.How to end this state without stopping SQL Server services?

If there's a need for multiple files restoring, CLI commands requires WITH NORECOVERY and WITH RECOVERY respectively - only the last file in command should have WITH RECOVERY to bring back the database online:

There is also virtual restoring process, but you'll have to use 3rd party solutions. Usually you can use a database backup as live online database. ApexSQL and Idera has their own solutions. Review by SQL Hammer about ApexSQL Restore. Virtual restoring is good solution if you're dealing with large numbers of backups. Restore process is much faster and also can save a lot of space on disk drive. You can take a look on infographic here for some comparison.

Right Click database go to Tasks --> Restore --> Transaction logsIn the transactions files if you see a file checked, then SQL server is trying to restore from this file.Uncheck the file, and click OK. Database is back .....

Then I right-clicked on Databases and selected Refresh which removed the entry in the SQL Server Management Studio (SSMS). Afterwards I did a new restore which worked fine.

By default, every RESTORE DATABASE comes with RECOVERY set up. The 'NORECOVERY' options, basically tells the SQL Server that the database is waiting for more restore files (could be a DIFF file and LOG file and, could include tail-log backup file, if possible).The 'RECOVERY' options, finish all transactions and let the database ready to perform transactions.

Ran into a similar issue while restoring the database using SQL server management studio and it got stuck into restoring mode. After several hours of issue tracking, the following query worked for me. The following query restores the database from an existing backup to a previous state. I believe, the catch is the to have the .mdf and .log file in the same directory.

I had the same issue... although I do not know why my database experienced this problem as my drive was not full... It's like it got corrupted or something. I tried all of the above none of them fully worked, I especially thought the suggestion to stop the service and deleting the mdf and ldf files would work... but it still froze up on restore?

I ended up resolving this by deleting the files as mentioned but instead of trying to restore the DB again I copied over fresh .mdf and .ldf files and Attached these using the Front End Attachment wizard. Relief, it worked!!

This is an old issue that keeps coming up all the time with SQL Server, even the latest 2019 version! I don't know why Microsoft have left this pain take hold for so long and allowed their MSSQL engine to continue behaving this way. Never the less, I propose another possible solution for those that tried the RESTORE DATABASE WITH RECOVERYoption and it still didn't work.

Log into the server itself and fire up the default SSMS program on the actual database server. Next go to the "recovery" database and DELETE it. Done. Problem gone. If you needed to keep it, copy the MDF file and rename it and attach it as a new database. Worked for me on SQL Server 2008 R2.

The reason is because the assignment of the 16 bit value stored in our variable test to our variable local is not what we call atomic. What do I mean by atomic? Essentially it means that the assignment takes multiple cycles for the microcontroller is performed. This is because the microcontroller is an 8-bit processor, so it only handles data in 8 bit chunks. When we are working with 16 bit data, the processor still only handles that data 8 bits at a time, so will always take multiple cycles to perform even the simplest operations on that data.