Post reply

Transaction log issue

  • July 31, 2008 at 1:01 am

    #190396

    Hi,

    How are you all !

    Ok let me tell you what is the issue Iam facing.

    I have two Database DEV and Test. I need to create a maintenance plan which will copy only the Tlog from the DEV to TEST DB everyday. THis will sych up the changes in the DEV DB with that of TEST DB.

    I tried doing manually but I receive this error message:

    Msg 3117, Level 16, State 4, Line 1

    The log or differential backup cannot be restored because no files are ready to rollforward.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE LOG is terminating abnormally.

    PLease help me out !!!!

    "More Green More Oxygen !! Plant a tree today"

  • July 31, 2008 at 2:03 am

    #851050

    That error means that the last backup that was restored to the database was restored WITH RECOVERY. With recovery means that transactions are rolled forward and back as necessary and the database is brought into an online, usable state.

    If you want to restore more transaction logs, the backups must be restored WITH NORECOVERY, meaning the database is left in a recovering state. It is inaccessible in that state but more log backups can be applied.

    Log shipping is not a suitable way to keep a dev and test box in sync, as the target for the log shipping is, at best, read only. Have you considered replication?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 31, 2008 at 3:01 am

    #851090

    Thanks Gila for the prompt answer !!! Yes I have considered Replication need to work on that !!!

    "More Green More Oxygen !! Plant a tree today"

  • July 31, 2008 at 3:07 am

    #851094

    shalu (7/31/2008)

    s it possible to deattach the log file and attach it over there. This will save time.

    No. If you want to detach, copy and attach it has to be the entire database. All of the data and log files.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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