SQL database stuck in restoring mode and unable to access required files. Let us understand all possible solutions. Restoring mode is always a big problem for administrator. But here in this post, you will find proven solution to fix SQL database stuck in restoring mode. Let’s get started!
Maintenance of SQL backup is the core requirement of the SQL Server.
But unfortunately, if the user runs the restoration process with NO RECOVERY command, it will end up SQL database stuck in the restoring status.
So, what is the solution to pull-out the database from there??
We will discuss the responsible reason and all the promising solutions to fix it.
One of the clients shared his query when he was turning a backup into a database. Here’s what he has to say
“My database is in restoring state that was started 20 hours ago. Generally, it takes only one hour.
sys.dm_exec_requests are not returning anything. I have two questions regarding this situation…
- To stop the restoring should I stop SQL? Delete database and restore again??
- Is there any command to check the percentage restored so I can decide what action to perform?”
How Come SQL Database Stuck in Restoring?
Reason-1. Often database is stuck in restoring after the restarting of the system. This can happen due to a large database when a long transaction is in progress and sudden/unexpected server shutdown/restart will automatically go for restoring the database.
Reason-2. When the user tries to back up the tail of the transaction log using the NORECOVERY option. It induces changes in the database state to a restoring state.
Reason-3. Another cause of SQL database stuck in the restoring state when the database is a section of the mirrored database. If the primary database is found to be faulty, the mirrored database will take over the database operations. Refer to this image:
Reason4. While SQL database is in use and in between network crashed or failed then it will lead to corruption in MDF files.
Not only these virus attacks, hard disk failures, sudden system/application shutdown, unexpected power breakdown also cause the corruption of files.
Corruption of MDF/LDF files will make files inaccessible and to make it workable MS SQL will put it in restoring mode. SQL recovery solution can directly fix this issue with data loss.
Additionally, changes in SQL Server account, accidental data deletion, and file header corruption also lead to corruption of files. Always cautious of the factors that cause corruption.
Methods to Fix SQL Database Stuck Issue
Now you might have an idea of why the SQL database stuck in restoring status.
If the user is curious to know the percentage of the database restored then he can execute the following command.
SYS.DM_EXEC_REQUESTS => PERCENT_COMPLETE;
It will return the values for the restoration process.
Error indications of SQL database stuck in restoring
Res31548 738 11/29 11:43:39 ### CVSimpleDBConnObject::OpenSQLConnectionUsingConnectionString() – Connecting to the database failed – error returned was: INFO: [Login failed for user ‘sqladmin_cv’.] [RecNum:1, State:28000, NativeErr:18456, Severity:-1, Spid:-1]
31548 738 11/29 11:43:40 ### CVSimpleDBConnObject::OpenSQLConnectionUsingConnectionString() –
Retrying in [1] seconds…
Connecting to the database failed – error returned was: INFO: [Login failed for user ‘sqladmin_cv’.]
So, now it’s time to fix the SQL Database Stuck in restoring status.
Method-1. Use the NORECOVER and RECOVERY command
Fix the stuck SQL restoring status using the following commands:
Step-1. Use NORECOVER parameter to fix stuck SQL restoring mode.
If you don’t have any additional backups to restore, use this command to recover the database.
RESTORE DATABASE [DB name] WITH RECOVERY
RESTORE DATABASE AdventurousWork FROM DISK =’C:\ AdventurousWork.BAK’
WITH NORECOVERY
GO
Use this command when you have multiple backup files, so if the process couldn’t process entirely then you can use other backup files. But remember to run the upcoming RECOVER command parameter on the last backup database.
Note – This command will not function if the database is locked by another user.
Step-2. Use the RECOVER parameter to fix restoring mode
RESTORE LOG AdventurousWork FROM DISK = ‘C:\ AdventurousWork.TRN’
WITH RECOVERY
GO
After executing this command, the database will be available to use. However, the user won’t be able to restore any additional backup to this database unless it is started all over again with a complete backup.
Method-2. Fix Issue Using SQL Server Services
- Quit SQL Server instances by clicking on the Stop option.
- Export database files to another drive/location or just rename them with MDF and LDF extension.
- Open Start SQL Server Service and by right-clicking on SQL Server Services and click the Restart option.
- Databases will appear in recovery mode.
- After removing all connections, set the database to offline.
- Right-click on the database and click on the Delete option.
- Rename the database files to the same location.
- Attach the database by right-clicking on the database and attach it as shown.
Now the database is recovered and is available for use.
You can check the SQL error logs on DBCC checks and count the number of transactions that were rolled back or forward during the recovery process.
The Buck Stops Here
If you couldn’t figure out what caused SQL database stuck in restoring status then it’s a wake-up call.
Examine your SQL database and comprehend the causes and keep the SQL recovery solution at hand to avoid any catastrophic events.