In point time recovery error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

In point time recovery error

Hello DBA’s, I have been trying to make an in point time recovery. and when I do that I get the following error: The log or differential backup cannot be restored because no files are ready to rollforward. Any help?? What might be the cause for this error
Thanks
Satya
Could you post all backup process you did?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
OK This is what I did:
backup log lites to disk=’C:SQLBackuplites_log.trn’ ( at 4:55 P.M) ( A full backup was taken 15 minutes before this) When I tried to restore the log to 4:47 P.M, restore log Lites from disk=’C:SQLBackuplites_log’ with recovery, STOPAT = ‘Jan 09,2007 4:47 PM’
When I run the above command, I get the error Thanks
Satya
What error you are getting? Run RESTORE HEADERONLY from disk = ” command to see the backup start/end times.. Make sure your STOPAT time should be less than backup finish time…
Mohammed U.
Check: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=370656&SiteID=17 Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
quote:Originally posted by satya.sqldba OK This is what I did:
backup log lites to disk=’C:SQLBackuplites_log.trn’ ( at 4:55 P.M) ( A full backup was taken 15 minutes before this) When I tried to restore the log to 4:47 P.M, restore log Lites from disk=’C:SQLBackuplites_log’ with recovery, STOPAT = ‘Jan 09,2007 4:47 PM’
When I run the above command, I get the error Thanks
Satya

How can u do this ? u r Transaction log backup is of 4.45 PM and u r trying to give stopat= 4:47′. To do this u would have taken the active TL backup. Madhu
Is this in SQL Server 2005 by chance? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
No Madhu I took my T LOg backup at 4:55 P.M and I was trying to restore it to 4:47 P.M Thanks
Satya
Yes Satya This is in SQL 2005 Thanks
Satya
Are you performing differential backups? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
To perform log restores you must first do a full database restore with no recovery.. that seems to be a missing step?
Thomas I didnt miss that step, I tried that once again to make sure abt it. I restored the database to the last full backup I have taken before the log backup, and when I try to restore it to point of time after the full backup and before the log backup, I get this error once again. Thanks
Satya
Satya I am doing log backup and not differential Thanks
Satya
Problem solved
I was getting that error because I did not put with norecovery option at the full backup that I have done. Anyway thanks for all the help fellow DBA’s Thanks
Satya
So you were trying to restore the tlog on RECOVERED db… Mohammed U.
Ya That caused the whole problem Satya
In 2000 if you try to restore on recovered db you get the different error than what you got ("The log or differential backup cannot be restored because no files are ready to rollforward.")… sql 2000 error:
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Mohammed U.
Ya Tht should have been more clear in 2000, but in 2005, it troubled me for so long time to know where exactly I was going wrong Thanks
Satya
]]>