September 26, 2014 at 8:30 am
We have a database to which we want to restore a full back and then restore several logs. But between log restores we have to RESTORE DATABASE [Cadence] WITH RECOVERY; so users can access the database.
When I try to restore another log with this command
RESTORE LOG [Cadence] FROM DISK = 'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\AMC_Backup\AMC_TLP\Cadence_backup_2014_09_19_070002_3397090.trn'
WITH NORECOVERY, FILE = 1;
I get :
Msg 3117, Level 16, State 4, Line 2
The log or differential backup cannot be restored because no files are ready to rollforward.
How do we put the database back into a state that we can restore the next log?
September 26, 2014 at 8:37 am
You've recovered the database so you cannot restore any more log backups.
Why did you have to RESTORE...WITH RECOVERY between the log restores?
September 26, 2014 at 10:50 am
Once you RESTORE ... WITH RECOVERY you cannot restore any more backups. Recovering a database finishes the recovery process and brings it online and usable.
If you need to restore another log backup, you're going to have to drop that restored DB and start over restoring the full backup. There's no way to put it back into a state where further log backups can be restored.
If you need the DB read only between restores, why aren't you restoring WITH STANDBY?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2014 at 11:56 am
It was the only way I knew how to put the database back so users could access it. Without running RESTORE DATABASE [Cadence] WITH RECOVERY. The db status says restoring as opposed to status = normal.
September 26, 2014 at 12:55 pm
ross.mason 49698 (9/26/2014)
It was the only way I knew how to put the database back so users could access it.
If users can access database then they can modify it. So, how do you expect the restore process to combine changes from the users and from log backup files?
September 26, 2014 at 2:19 pm
We need this database restored locally so we can report from it. We will get a fullback in the morning at 4:00am. We will get a transaction log from the ops database each hour thereafter until 7:00pm.
Is the best practice to Reload the Full DB and bring it back in line?
Then when the first TL comes in restore the full DB and the TL and bring the DB back on line.
An hour later restore the full DB and TL 1 and TL 2 and bring the DB back on line.
An hour after that restore the full DB TL1, TL2 and TL3 and bring the DB back line?
September 26, 2014 at 2:22 pm
Read up on RESTORE in BOL (Books Online). If you are doing reporting from the database it sounds like you maybe able to use the WITH STANDBY option.
September 27, 2014 at 4:28 am
Again......
If you want the DB readable between restores, why are you not restoring WITH STANDBY?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2014 at 12:13 pm
Another possibility might be to use a differential backup to do the forward recovery rather than applying logs, particularly if the log process is slow.
Take a differential backup on the main database to the time you want to restore to, then apply it to the backup to bring the db up to that time in one operation. You can do WITH RECOVERY then and have the entire db available. If necessary, you can even apply a log file(s) from after the differential if there's additional activity you need to apply to the db.
Edit: Although, of course if you want to apply a log(s) after the differential is applied, you must apply the differential WITH NORECOVERY.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 29, 2014 at 12:28 pm
The only way the vendor will send us our data is a full back up in the morning with logs every hour. I have resigned myself to restoring the full log and applying all of the logs each hour. The actually work will be done in a c# app.
Thanks for your suggestions
September 29, 2014 at 12:36 pm
ross.mason 49698 (9/29/2014)
The only way the vendor will send us our data is a full back up in the morning with logs every hour. I have resigned myself to restoring the full log and applying all of the logs each hour. The actually work will be done in a c# app.Thanks for your suggestions
Ok, I understand. If it's an hourly process, you can pre-restore the backup for the next log's recovery to a different db name, use the log to recover that name, then just rename the dbs to make the new db the active one.
Say the actual db is "main_db". You could restore the backup to "main_db_restoring" (or _restoring01, _restoring02, etc.). Apply the log to _restoring; when it's fully recovered, DROP DATABASE "main_db", and rename "main_db_restoring" to "main_db". Those commands will take only a couple of seconds, although existing activity on "main_db" will be lost, of course.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 29, 2014 at 12:47 pm
Your idea looks pretty slick. Let me work with db and test it. However, it may be tomorrow before I can.
Thanks
September 30, 2014 at 7:49 am
Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
This reminds me of an old Network World cartoon that I used to have taped up on my wall:
"There is no such thing as a dumb question."
Question: "Aren't there two m's in dumb?"
October 6, 2014 at 4:36 am
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply