FULL BACKUPs vs TRANSACTION LOG BACKUPs

  • Hi everyone,

    Can anyone explain simply the difference between LDF data and TRN data.

    I am a bit confused.

    I am backing up every night not more than total 2 Gb as a full-backup. It takes only few minutes. Do I really need to set a Transaction Log Backup Plan as well?

    Thanks,

    /Day

  • day (8/27/2008)


    I am backing up every night not more than total 2 Gb as a full-backup. It takes only few minutes. Do I really need to set a Transaction Log Backup Plan as well?

    Depends. If the drive crashes 10 min before the full backup would have run, can you afford to lose a full day's data? To answer the question of tran log backups, and how frequent they need to be, you need to know how much data loss is 'acceptable' in the case of a disaster.

    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
  • Can anyone explain simply the difference between LDF data and TRN data

    LDF file contains transaction log. Every database must have atleat one .LDF file.

    TRN file contains transaction log backup. It will be created, only if you take log backup.

  • If you only have a full backup you can only restore to that full backup. So if your database dies towards the end of the day you can only go back to the last full backup and will lose all of the latest data.

    If you have transaction log backups as well (frequency depends on factors such as recovery requirements) you will be able to restore to the latest log backup and therefore not lose much data. You can also do point-in-time recovery if you have transaction log backups. For most live systems a transaction log backup is required for these reasons.

  • Thanks everyone,

    I have a better understanding now.

    I would like to know when we restore a full back up, we restore full LDF if our recovery model is "full". What is it stored in LDF. Why we need full LDF file? We already restore the full backup. Even though LDF file is 0 byte we still have the full data if there is no change after the backup time.

    I can understand TRN file and why we need them.

    In which case, we would need full LDF data?

    Thanks again.

  • Maybe these two blog posts will help?

    http://www.sqlskills.com/blogs/paul/2007/10/25/DebunkingACoupleOfMythsAroundFullDatabaseBackups.aspx

    http://www.sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx

    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 6 posts - 1 through 5 (of 5 total)

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