SQL SERVER – Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward

I received the following email from one of my readers.

Dear Pinal,

I am new to SQL Server and our regular DBA is on vacation. Our production database had some problem and I have just restored full database backup to production server. When I try to apply log back I am getting following error. I am sure, this is valid log backup file. Screenshot is attached.

[Few other details regarding server/ip address removed]

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

Screenshot attached. [Removed as it contained live IP address]

Solarwinds

Please help immediately.

Well I have answered this question in my earlier post, 2 years ago, over here SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward. However, I will try to explain it a little more this time.

For SQL Server database to be used it should in online state. There are multiple states of SQL Server Database.

  • ONLINE (Available – online for data)
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY (Limited Availability)

If the database is online, it means it is active and in operational mode. It will not make sense to apply further log from backup if the operations have continued on this database. The common practice during the backup restore process is to specify the keyword RECOVERY when the database is restored. When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.

However, if you want to restore more than one backup files, i.e. after restoring the full back up if you want to apply further differential or log backup you cannot do that when database is online and already active. You need to have your database in the state where it can further accept the backup data and not the online data request. If the SQL Server is online and also accepts database backup file, then there can be data inconsistency. This is the reason that when there are more than one database backup files to be restored, one has to restore the database with NO RECOVERY keyword in the RESTORE operation.

SQL SERVER - Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward recoverybackup

I suggest you all to read one more post written by me earlier. In this post, I explained the time line with image and graphic SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.

Sample Code for reference:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksFull.bak'
WITH NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksDiff.bak'
WITH RECOVERY;

In this post, I am not trying to cover complete backup and recovery. I am just attempting to address one type of error and its resolution. Please test these scenarios on the development server. Playing with live database backup and recovery is always very crucial and needs to be properly planned. Leave a comment here if you need help with this subject.

Similar Post:
SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

Note: We will cover Standby Server maintenance and Recovery in another blog post and it is intentionally, not covered this post.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Microsoft SQL Server Protocol Documentation Download
Next Post
SQL SERVER – Enumerations in Relational Database – Best Practice

Related Posts

27 Comments. Leave new

  • Thanks for sharing this article, i too had my share of issues with restoring backup’s in absence of dba’s, as you rightly mentioned, it is essential to have a good understanding of the back and recovery models in sql server.

    Reply
  • I had an issue on a database and updated the columns wrongly.
    Now its a live database with active financial data.

    I need to take a tlog backup and i can not afford to take the db offline.
    What I want to do is to restore a full backup on a new db and restore subsequent backups; then I can copy the values from the old restored database.
    But to get the db into a state where it was previously before the issue, I need a log backup.
    Can I take a log backup and ask it to restore to a particular set of backups apart from the live db (fulll, diff, prev tlog backups)?
    DO i really need to take a tlog backup that will put the db in recovery state to achieve this?

    Reply
  • Dear Pinal!
    I have a little problem with this issue. I read all your posts but didn’t find the solution to my problem ! Please help me!
    I have a full backup database and a few tlog files right now and I’m going to get the new tlog file from the client every week . The thing is I have to work with the database.
    So I would like to copy it in order to be able to work with , but because another transaction log files has to be applied later, I haveto leave this database in NORECOVERY MODE. The problem is I cannot copy database in “NORECOVERY” MODE.
    So what can I do If I want to be able to work with the data (to get access to the data) and in the same time to leave it “NORECOVERY” to be able to apply further transaction log files?
    Thank you in advance
    Anna

    Reply
    • I am facing the same problem as Anna. I have a full backup and periodic transaction log files – I am doing SQL log shipping but I want to be able to use the database (read-only) between restoring transaction log files. How can I do that?

      Reply
      • Filip Vasilev
        March 11, 2012 4:57 am

        Anna & JohnR

        1. Use the same FULL back up to restore it with different database name, let’s call it dbTest1. Studio manager will take care of changing the mdf and ldf file names. Use NORECOVERY option again so you can restore all the transaction logs you have. However, when restoring the last transaction log backup use RECOVERY option so the database can be readable after.

        *This is the process you will have to repeat every time you get a new transaction log. Just use REPLACE option when restoring the full backup or simply drop database dbTest1 and repeat the step 1.

  • Anna & JohnR
    you can restore your full backup with “stand by” option.this can leave your database in stand by mode that you can use it! Note that if your database be in a “norecovery” mode , you can never quering it !

    Reply
  • Hi;

    Great artical, but let me add another question here.

    As a human error, if anybody restore the last differential backup with NORECOVERY option (Mistakenly) and just want to bring database online, what you suggest for this case, thanks.

    RESTORE DATABASE AdventureWorks
    FROM DISK = ‘C:AdventureWorksFull.bak’
    WITH NORECOVERY;
    RESTORE DATABASE AdventureWorks
    FROM DISK = ‘C:AdventureWorksDiff.bak’
    WITH RECOVERY

    Reply
  • You can just run

    RESTORE DATABASE AdventureWorks WITH RECOVERY

    Reply
  • Thank you Pinal for the post, it helped me.

    Reply
  • thanks

    Reply
  • senthilkumar
    April 3, 2013 4:59 pm

    Restoring FULL database backup and then differential backup works fine then why we need to restore transaction log backup

    Reply
    • senthilkumar
      April 3, 2013 5:13 pm

      I hope if we are not transaction log we might loss data which are created after the differential backup

      Reply
  • Hannah Lee Pei Wai
    June 21, 2013 7:06 am

    How do I query the SQL Server database as you mentioned for the status below?

    ONLINE (Available – online for data)
    OFFLINE
    RESTORING
    RECOVERING
    RECOVERY PENDING
    SUSPECT
    EMERGENCY (Limited Availability)

    Reply
  • it is very useful to identify the difference between no recovery and recovery database.

    Reply
  • Thank you Pinal. You provided the reminders of how to do this correctly.

    Reply
  • Can i restore the differencial without the full backup or it’s impossible ???

    Reply
  • thanks I can never remember how to do this.

    Reply
  • Still m gettin this error plj help me out.. m trying to set mirroring

    Reply
  • hello Sir,

    I have done restoration of full backup at DR location from DC location.I have a problem, when i am restoring transaction log file at DR location through GUI, it is not allowing me to do that by having grey color transaction log . please help me…..

    Reply
  • Hi Pinal here iam having an issue with this backups restore we are having a Scenario like to restore the diff backup daily to update the DB in separate server but every time iam facing this issue “The log or differential backup cannot be restored because no files are ready to rollforward.Msg 3013, Level 16, State 1, Line 1” by that time i have to restore full and then this diff backup but this is time consuming for me a lot is there any simple solution to save my time and to restore this diff dackup as-well thanks in advance. :)

    Reply
  • Hi Pinal, Love reading your blogs and thanks for so much detailed information.
    Just being a beginner, how to restore only transaction logs without having to restore the backup.
    As we are just getting the transaction logs daily from the product company and they have given us full backup only once.Please help me in knowing how to go about this scenario

    Reply
  • What gkarung said. Thanks for breaking down RECOVERY and NORECOVERY.

    Reply
  • pinal it doesn’t work i still have the same error

    Reply

Leave a Reply

Menu