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.
Msg 4523, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Before moving 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 the production server. However, nothing
to panic at this stage. Let’s check for the two scenarios to resolve the issue.
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 that 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.
You may also refer:
"Creating a COPY_ONLY Backups in SQL server"
"Creating a COPY_ONLY Backups in SQL server"
0 comments:
Post a Comment