SQL Server Performance

In point time recovery error

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Jan 9, 2007.

  1. satya.sqldba New Member

    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

  2. Luis Martin Moderator

    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.



  3. satya.sqldba New Member

    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
  4. MohammedU New Member

    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.
  5. Luis Martin Moderator

  6. madhuottapalam New Member

    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
  7. satya Moderator

    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.
  8. satya.sqldba New Member

    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
  9. satya.sqldba New Member

    Yes Satya

    This is in SQL 2005

    Thanks
    Satya
  10. satya Moderator

    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.
  11. thomas New Member

    To perform log restores you must first do a full database restore with no recovery.. that seems to be a missing step?
  12. satya.sqldba New Member

    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
  13. satya.sqldba New Member

    Satya

    I am doing log backup and not differential

    Thanks
    Satya
  14. satya.sqldba New Member

    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
  15. MohammedU New Member

    So you were trying to restore the tlog on RECOVERED db...

    Mohammed U.
  16. satya.sqldba New Member

    Ya

    That caused the whole problem

    Satya
  17. MohammedU New Member

    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.
  18. satya.sqldba New Member

    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

Share This Page