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

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

10 thoughts on “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

  1. What does “Make sure database is not in operational conditional when differential backup is attempted to be restored” mean?

    Like

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

    Like

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

      Like

  3. Pingback: SQL SERVER – Fix : Error : 3117 : The log or differential backup cannot be restored because no files are ready to rollforward Journey to SQL Authority with Pinal Dave

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

    Like

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

    Like

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

    Like

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