locked
Transaction Log Replay RRS feed

  • Question

  • Hi all,

     I am restoring a sql server backup for the first time as well as the most recent traction logs.  I'm not having any problems with the backup/restore per se, just a question/curiosity about the transactions logs.  I am using a script and at one point I ran the script accidentally a second time and noticed that all transaction logs failed to apply during the second run (as expected as they were already applied during the first run) except for the most recent log.  It seems to apply cleanly. Is this normal behavior?  This log file was not a new log since the previous restore, it is the exact same set of logs that were applied the first time.  I seem to be able to restore the most recent transaction log as many times as I like, is this normal?

    For reference, here is the portion of the script I am using to restore the logs

    FOR /f %%b IN ('DIR /b %TLOG_PATH%\%DB_NAME%\*.trn')
    DO (
    sqlcmd -Q "RESTORE LOG %DB_NAME% FROM DISK = '%TLOG_PATH%\%DB_NAME%\%%b' WITH NORECOVERY"
    )

    Monday, June 10, 2013 7:05 PM

Answers

  • hi ,

    thats normal behaviour..but ur restoring all logs in no recovery ..last trn log should be restored with recovery ...

    Trn log restore looks for LSN (log sequence no ) durin time of restore if you restore too old or too recent trn logs it will flash u with error..since u restored properly i assume restore sequence was correct..



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Tuesday, June 11, 2013 10:48 AM
    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:57 AM
    • Marked as answer by xGumbyx Wednesday, June 12, 2013 6:02 PM
    Tuesday, June 11, 2013 5:50 AM

All replies

  • hi ,

    thats normal behaviour..but ur restoring all logs in no recovery ..last trn log should be restored with recovery ...

    Trn log restore looks for LSN (log sequence no ) durin time of restore if you restore too old or too recent trn logs it will flash u with error..since u restored properly i assume restore sequence was correct..



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Tuesday, June 11, 2013 10:48 AM
    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:57 AM
    • Marked as answer by xGumbyx Wednesday, June 12, 2013 6:02 PM
    Tuesday, June 11, 2013 5:50 AM
  • Hello,

    If you restore the transaction log backup with RECOVERY option,the database will rolling back uncommitted transactions. Additional transaction logs cannot be restored. If you want to apply multiple transaction log backups, leaves the database unrecovered by "RESTORE WITH NORECOVERY".
    Reference:http://msdn.microsoft.com/en-us/library/ms177446.aspx

    Regards,

    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, June 12, 2013 8:57 AM
  • Thanks to the both of you for your responses.  It seems odd to me that I can replay the same transaction log twice with each time telling me that the replay was successful.  I'm leaving the db unrecovered on purpose as this machine is not in production use and is being used as a backup (not hot standby) for the first.
    Wednesday, June 12, 2013 6:04 PM