How to Fix Error “The database cannot be recovered because the log was not restored”?

Summary: Is your SQL Server database stuck in RESTORING state and returns the error ‘The database cannot be recovered because the log was not restored’. In this post, learn the possible reasons behind this SQL Server error and the solutions to fix it. Also, you’ll get to know about other problems that might occur during the restore process. You’ll also find about an advanced SQL recovery tool that can help restore the database without any data loss.

Sometimes, when attempting to restore a SQL Server database, you encounter a situation where the database stuck in the Restoring state and displays the following error message:

Msg 4333, Level 16, State 1, Line 1

The database cannot be recovered because the log was not restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

What causes the error?

There are no known reasons as such that particularly cause this error. However, the error may occur due to any of these reasons:

• Your SQL Server instance got disconnected while restoring any transaction log backups.
• The restore process was cancelled.
• Server ran out of disk space while restoring the database.

How to fix the error?

Note: Before proceeding, ensure that the server has sufficient disk space and then try to restore the database. If you don’t have disk space issues, then follow the solution discussed below.

To resolve the issue, try to drop the problematic database and restore it again. To do so, execute the following in the query window in SQL Server Management Studio (SSMS):

Note: If you cannot DROP the database, try to detach the database using the sp_detach_db stored procedure. If the stored procedure returns the error ‘database is in use’, you can use the dbcc detachdb command to detach the database. However, you need to be careful as this is an undocumented command.

USE [master]

GO

-- Create the db again with the same name as the original one

CREATE DATABASE [database_name];

-- restore the database using the usual backup scripts

RESTORE DATABASE database_name

FROM DISK = 'enter path of .bak file'

WITH RECOVERY

Note: If you need to restore multiple database files, run the RESTORE command WITH NORECOVERY option. For the last database file, run the RESTORE command WITH RECOVERY.

What to do if the database turns inaccessible?

If you can restore the database but it is no longer accessible and marked as SUSPECT, you will need to repair the database. Keep in mind that you cannot repair a SUSPECT database. You will first need to make the database accessible by putting it in EMERGENCY mode. To do so, execute the below command:

ALTER DATABASE database_name SET EMERGENCY

Once the database becomes accessible, it’s vital that you have exclusive access to the database. So, set the database state to SINGLE_USER mode and run DBCC CHECKDB with the ‘REPAIR_ALLOW_DATA_LOSS’ option.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('database_name',REPAIR_ALLOW_DATA_LOSS)

After that, put the database back in MULTI_USER mode by executing the following command:

ALTER DATABASE database_name SET MULTI_USER

Read in detail: How to Recover MS SQL Database from Suspect Mode?

Refresh the database and see if you can access it.

If the database is still inaccessible and you need to avoid any data loss, use a SQL recovery tool that can help restore the database while keeping its data intact. You can use Stellar Repair for MS SQL to repair the database (MDF) file and restore it to its original state. Also, the software helps restore the database with 8X speed by allowing you to save multiple (up to 8 tables) simultaneously.

The software can also help restore deleted records from a SQL Server database. In addition, you can preview the repaired components before saving them to an existing database, new database, or other file formats (CSV, HTML, or XLS).

Conclusion

If you are getting ‘The database cannot be recovered because the log was not restored’ error, dropping the database and restoring it from backup might help fix the error. If restoring fails or the database has turned inaccessible, try repairing the database using the DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS command. If this doesn’t help, you can use Stellar Repair for MS SQL software to repair and restore your database.  

Related Post