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]

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.

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 (http://blog.SQLAuthority.com)

About these ads

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

  1. 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.

  2. 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?

  3. 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

    • 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?

      • 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.

  4. 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 !

  5. 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

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

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

  8. 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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s