Tuesday, 20 February 2018

How to Resolve Error - The log or differential backup cannot be restored because no files are ready to rollforward

It was when one of my friend who got paused at his work when he received an error message as

Msg 8766, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 4523, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Before to move on to resolve the issue, lets first check the status of the database from which the error is being generated. The database status could be either in ONLINE, OFFLINE, RECOVERY, RECOVERY PENDING, RESTORING, SUSPECT or EMERGENCY (Limited Availability).

Resolution:

In almost all the status of the database mentioned, there could be two possible scenarios of the database, where we can experience this error. While I was checking with my friend regarding the issue, he was stuck at a point when the database is live and running with critical data in production server. However, nothing to panic at this stage. Let’s check for the two scenarios to resolve the issue.

 Solution 1:

In case, if you are trying to restore on a live database, which is already restored with “WITH RECOVERY” then there is no point of either applying Differential backup or T-LOG backup on an existing live database. If your latest Full and Differential backups are ready on your disk for the concerned database, then proceed to restore the Full backup “WITH NORECOVERY” and then rest data with the Differential backup “WITH RECOVERY”.

RESTORE DATABASE SQLArena_TEST
FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'
WITH NORECOVERY; -- with NORECOVERY means still, there are backups to be restored

RESTORE DATABASE SQLArena_TEST
FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'
WITH RECOVERY; -- with RECOVERY means there are no backups pending to be restored

NOTE: This can resolve only if your backups are consistent. 

Solution 2:

In case, you try the Solution 1 and fail to succeed, then try to take a full backup of the concerned database for both Full backup and Differential backup. Once, the Full and Differential backups are ready, try to restore Full backup with “NORECOVERY” and Differential with “RECOVERY”. This can for sure resolve your issue from erroring out.

BACKUP DATABASE SQLARENA_TEST
TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'
WITH NOFORMAT, STATS = 10 -- full backup

BACKUP DATABASE SQLARENA_TEST
TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'
WITH NOFORMAT, STATS = 10 -- differential backup

RESTORE DATABASE SQLArena_TEST
FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'
WITH NORECOVERY; -- with NORECOVERY for full backup

RESTORE DATABASE SQLArena_TEST
FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'
WITH RECOVERY; -- with RECOVERY for differential backup

Please do let me know if this doesn’t work as there are many other possible situations which could be solved in a different way.

Important Note: Please try not to test these on production databases, which can probably mess around. For testing purposed please try in test or development servers. 

0 comments: