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

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Sun, 21 Mar 2010 01:30:28 +0000 Indexed on 2010/03/21 1:31 UTC
Read the original article Hit count: 1074

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)


Filed under: Pinal Dave, Readers Question, SQL, SQL Authority, SQL Backup and Restore, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about Pinal Dave

Related posts about Readers Question

  • Ask the Readers: Share Your Tips for Defeating Viruses and Malware

    as seen on How to geek - Search for 'How to geek'
    We’ve shared some of our best tips for dealing with malware over the years, and now it’s your turn! Share your favorite tips for protecting against, or getting rid of viruses and other types of malicious software. Unfortunately, if you’re a PC user it’s a given that you have to play defense against… >>> More

  • test questiontest question test question test question? [closed]

    as seen on Stack Overflow - Search for 'Stack Overflow'
    TestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestionTestQuestio… >>> More

  • PHP Browser Game Question - Pretty General Language Suitability and Approach Question

    as seen on Game Development - Search for 'Game Development'
    I'm developing a browser game, using PHP, but I'm unsure if the way I'm going about doing it is to be encouraged anymore. It's basically one of those MMOs where you level up various buildings and what have you, but, you then commit some abstract fighting entity that the game gives you, to an automated… >>> More

  • Testing on Device Other Than the Known Brand Question (Local and Imported Phone Question)

    as seen on Game Development - Search for 'Game Development'
    I have a question. When testing a device by using Eclipse, it's easy to install and add device software with these specific brands commonly used in game testing like Samsung, Google, T-Mobile, and HTC; according to the Android Developers website. What if I'm using other brands that runs on Android… >>> More

  • question about prime question

    as seen on Stack Overflow - Search for 'Stack Overflow'
    i know that there are many binary operations to show that something is true for example we can show if number is power of two or something else is there some theory or special binary method to show if number is prime? thanks >>> More