Post reply

Complete Backup and Transaction Log Backups at the same time (almost)

  • January 15, 2008 at 2:37 pm

    #154067

    If the database Recovery Model is Full and there are complete backups taken at 3 A.M. and transaction log backups taken at the same time (or about 5 minutes later) via the Maintanance Plans, is there any need for the transaction logs? In this scenario, wouldn't the committed transactions (changes) in the transaction logs already be included in the full database backups? (We are running a SQL Server 2000 Database on Windows 2003.)

    Thanks in advance, Kevin

  • January 15, 2008 at 2:54 pm

    #768357

    You need to run transaction log backups to keep from filling up the transaction log file.

    I recommend that you run transaction log backups much more often, like every 15 minutes 24x7.

  • January 16, 2008 at 6:56 am

    #768620

    I am using this scenario to get a better understanding of the Transaction Logs. If I take a complete backup at 3 A.M. and run Transaction Logs Backups every 15 minutes and there is a crash at 9 A.M. Do I need to restore the complete backup from 3 A.M. and then restore each transaction log backup which was created every 15 minutes up until the crash occurred? Or do I just need to restore the complete backup from 3 A.M. and the latest transaction log backup taken before the crash at 9 A.M?

    Thanks, Kevin

  • January 16, 2008 at 7:21 am

    #768638

    A full backup cntains the trans log info, but it does not truncate inactive portions of the log. Only a log backup does that.

    Each time a transaction log backup occurs, the current log is backed up and the inactive portions then removed from the log file. Hence transaction log backups are incremental.

    You would need to restore the full, then each log backup, in sequence, from the time of the full backup until the last log backup that you have. If you managed to backup the tail of the log after the crash, you would then restore that and the db would be back to how it was when the failure occured.

    If you skip one of the log backups, the next one in the sequence will not restore. You would get an error about LSN mismatch (LSN = Log Sequence Number)

    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

  • January 16, 2008 at 7:41 am

    #768658

    Or you can perform a "differential log backup". Meaning - once a day you perform a log backup in its "default" format (which truncates the log for you), and the other transaction log backups get tagged with the WITH NO_TRUNCATE option. Your backups get bigger, but you would only need the latest transaction log backup at that point.

    The decision then hinges on how big the transaction log might get to be without truncating it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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