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 (https://blog.sqlauthority.com)
28 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.
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?
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.
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 !
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
You can just run
RESTORE DATABASE AdventureWorks WITH RECOVERY
Thank you Pinal for the post, it helped me.
thanks
Restoring FULL database backup and then differential backup works fine then why we need to restore transaction log backup
I hope if we are not transaction log we might loss data which are created after the differential backup
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)
it is very useful to identify the difference between no recovery and recovery database.
Thank you Pinal. You provided the reminders of how to do this correctly.
Can i restore the differencial without the full backup or it’s impossible ???
thanks I can never remember how to do this.
Still m gettin this error plj help me out.. m trying to set mirroring
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…..
What do you mean by “grey color transaction log”? Can you post it to https://www.facebook.com/sqlauth
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. :)
if database is already recovered using previous restore than further backups can’t be restored.
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
What gkarung said. Thanks for breaking down RECOVERY and NORECOVERY.
Thanks Ken.
pinal it doesn’t work i still have the same error
I think restore sequence is not same as backup. have you taken any copy_only backup?