Following error occurs when tried to restored the differential backup.
Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward
Fix/WorkAround/Solution:
This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored.
Example of restoring differential backup successfully after restoring full backup.
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksFull.bak'
WITH NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksDiff.bak'
WITH RECOVERY;
Reference : Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
What does “Make sure database is not in operational conditional when differential backup is attempted to be restored” mean?
Thank you, this is indeed the solution to my problem!
Thanks for help
About 2 weeks ago I was sent a full database backup from our client to restore at my work site (We are basically trying to maintain a copy of the client production databse on our site for data analysis and reporting).
I eventually restored the database .bak file (140 GB’s) with default settings which means it was restored with the “Recovery” option which puts the DB back online as soon as the database restore is done.
Fast forward 4 weeks later………I have been sent 62 transaction log files to restore (tack on) to the above referenced database, to bring our data uptodate. Now my problem is that I am trying to figure out how to convert or switch my database back to “NORECOVERY” mode so that my transaction log backups can be restored with a command such as the one below:
RESTORE LOG Database_name
FROM DISK = ‘F:\filepath\Database_name.trn’
WITH NORECOVERY
Currently when I run this commend I get this error:
“Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.”
Which means that my database is in the wrong state. Can someone please tell me how to switch my DB to “NORECOVERY” mode, because I do not want to have to redo a full database restoration – this time leaving the database in NORECOVERY mode so that I can restore the additional 62 transactional log files.
I would also like to mention that I’m going to keep receiving transactional log files for the next 10 months or so, so what is the best way to approach restortion without having to do a full database back-up everytime and then having to apply a ton of transactionals just to keep my data uptodate? I just want to be able to apply the latest transactional logs (4 log files per day) and be able to have multiple user have read access to the database…
Thanks for all your help
Manish Kumar Dongre
Manish,
I have the same situation that you mentioned.
I have the server A and server B.
I did the full backup on server A. I will have transaction log backups on server A every day (4 per day).
I need to restore those backup on Server B and the end-user has to be able to “read” the table on Server B.
I read some articles but I couldn’t find the answer for that specific question.
Hi Pinal,
I have implemented log shipping.
It was working fine for me but for testing purpose i have removed logshipping.
and now just want to use secondary database.
for that i have restore database but i forget to restore transaction log when the database is in recovery mode.
my problem is how to restore rest of transaction log.
I tried that but getting error “Log can not be restored because no file are ready to rollforward.”
how to resolve this.
Thanks
Rajat
Hi ,
I have implemented log shipping from server A to server B .
Now If made schema changes on server A , will the effect takes place on server B? If I create a new table in a Database involved in log shipping on server A , will the table be present on the Database on the server B after the log shipping is performed???
GO
RESTORE DATABASE [DisasterDatabase]
FROM DISK =’D:\TEST\backup\DisasterDatabase.bak’
WITH REPLACE,
STANDBY = ‘D:\TEST\backup\ROLLBACK_UNDO_DisasterDatabase.BAK’
RESTORE LOG [DisasterDatabase]
FROM DISK = ‘D:\TEST\backup\DisasterDatabase.trn’ WITH recovery, FILE = 1, NOUNLOAD, STATS = 10
GO
THIS IS WHEN I GET THE ABOVE DISCUSSED ERROR.. I CANNOT USE WITH NORECOVERY OPTION WITH STANDBY OPTION…CAN U HELP ME OUT WITH THIS?