Post reply

transaction logs saying to late too apply, newer one available

  • August 4, 2006 at 9:43 am

    #153121

    HI everyone, Im in the process of trying to restore a database using a full backup, differential and transaction logs.

    The full backup was taken at 5am Sunday morning, differential today at 5am, then the transactions logs were done at 6am and 10am. 

    When doing the restore, I created a new database. Then applied the full and differential backups. They worked fine but I receive the error message below when trying to restore the transaction logs: 

    oft SQL-DMO (ODBC SQLState: 42000)

    ---------------------------

    The log in this backup set begins at LSN 14872000007449300001, which is too late to apply to the database.

    An earlier log backup that includes LSN 14869000000770300001 can be restored.

    RESTORE LOG is terminating abnormally.

    The database has full as recovery mode and we are not doing any log shipping. The only other thing is maintenance plans on Sunday at 1am.

    Also, I was able to do the restore by clicking on the database and on the general tab all the backups (full backup, differential, transaction logs) where already checked off for me. I just changed the name in the restore as database box and it worked fine. My dilema is...I need this to happen on another server where I will not be able to right click this database and hit restore.  Why am I receiving the error message about the t-logs when i try to do the restore manual?

    Now I can't do anything with the database because it says it's loading because I left it open to restore the transaction logs.

    I read the other posts on this subject but we aren't log shipping. WHat else can the problem be?

    Any help here would be greatly appreciated. Thanks a bunch

  • August 4, 2006 at 10:00 am

    #653375

    Check the order of transaction backup and compare with the full backup

    Kindest Regards,

    Amit Lohia

  • August 4, 2006 at 11:03 am

    #653393

    The full backup was done on Wednesday (mistake in earlier post) at 5am, differential today at 5am, and the transaction logs are every 4 hours from 6am until 10pm. This is how the backups are:

    Full backup Wednesday 5am

    Differential Friday 5am

    Logs:

    Thursday 2pm

    Thursday 6pm

    Thursday 10pm

    Friday 6am

    Friday 10am

     

    Logs overide if older than 24 hours.

    I restored the full backup from Wednesday (mistake in my earlier post) at 5am, then the differential today at 5am and then then when I tried to do the transactional log from today at 6am..It bombed. 

    Is this the wrong order? But when it was done automatically by SQL (using the same sequence it works)..which leads me to beleive it is me.  What is different when i do it manually...creating a new database and then applying the backups in sequental order and leaving the database non operational?

  • August 4, 2006 at 2:48 pm

    #653433

    what is the syntax of the entire restore process you are using, for the full, the diff, and the logs?

  • August 7, 2006 at 7:41 am

    #653562

    What do you mean by syntax? Please explain.

  • August 7, 2006 at 2:11 pm

    #653730

    You are using some scripts like:

    RESTORE DATABASE YourDB

    FROM  DISK = N'F:\MSSQL\BACKUPS\YourDB.BAK'

    WITH  REPLACE, 

    MOVE N'YourDB_Data' TO N'H:\data\YourDB_Data.MDF',

    MOVE N'YourDB_Log' TO N'G:\Log\YourDB_Log.LDF'

  • August 8, 2006 at 1:44 pm

    #653916

    4 questions: Was there a differential backup on Thursday 5 am as well? Was it to the same file as the Friday differential? Did you use WITH INIT on the backup? Did you use WITH FILE = on the restore? If I were psychic, i'd say your answers are Yes, Yes, No, No.

    Use RESTORE HEADERONLY to explore the contents of the differential and log backups. I'm guessing that you restored the differential from Thursday when you thought you were using Friday's.

  • August 8, 2006 at 2:02 pm

    #653920

    That's why it's important to make sure on restores you know what you're dealing with.

    The restore HEADERONLY comment above is a great tool.

    and syntax is what is mentioned above:

    how are you restoring the databases?

    I typically do so through query analyzer

    restore database

    FROM DISK = 'c:\backup path\full_file.bak'

    WITH NORECOVERY, stats = 10

    RESTORE DATABASE

    FROM DISK = '...\diff_file.bak'

    WITH NORECOVERY, stats = 10

    --note log below! and change in recovery

    RESTORE LOG <dbname

    FROM DISK = '...\tran_file.bak'

    WITH RECOVERY

    again, these are off the top of my head, syntax may be off.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply