SQL Error: The log or differential backup cannot be restored because no files are ready to rollforward.

Problem:

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

Solution:

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.

Advertisements

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