Tail backup/restore question

  • Hi guys,

    I have two jobs that are taking care of backups: a full backup and a log backup. The log backup job doesn't touch the tail (active log) because of the message: "Back up the tail log, and leave the database in the restoring state". The "...leave the database in the restoring state" puts me off.

    So in the case of an awry event, I only have at my disposal the full backup + the backed up transaction logs, WITHOUT the last transactions in the active log right before the crash.

    How can I get those last transactions from the active log?

  • Daniel C (6/26/2009)


    How can I get those last transactions from the active log?

    Do a last log backup and select the option "Back up the tail log, and leave the database in the restoring state". That will take the last of the log records, even if the mdf is damaged, and put the db into the state "restoring" so that no one can add any more data.

    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
  • If, at the time of failure you still have access to the server and the transaction log, that is when you do a tail backup of the log file. If you don't have access to the server and the log file, then you can only recover to the last backup file for which you have access.

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

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