Post reply

about backup

  • March 20, 2012 at 9:05 am

    #252973

    Hi all

    pls guide me abt the database backup

    full database back up&differential database backup.

    I mean im getting confused about taking full database backup & then transaction log backup.Do we have to take first full database backup and then transaction log backup all the time??please guide me

  • March 20, 2012 at 9:49 am

    #1461730

    Hi,

    Scenario 1.Daily full backup @10PM on sunday and every hour transaction log backup.(if more space to hold backups)

    Scenario 2: Weekly full backup @10PM on sunday and remaining every days take differential backup @10PM and every 5 hours take transaction log backups.(if less space to hold backups)

    Hope it will be useful in designing backup strategy.

    Thanks

    Kumar

    SQLDBATASK.blogspot.com

    Regards,
    Kumar

  • March 20, 2012 at 9:58 am

    #1461736

    Stop thinking about backups.

    What are your recovery SLAs for this database? If the DB should disappear one day, how long would you have to recover it and how much data loss would be allowed. From those requirements, plan a backup strategy that will allow you to meet the requirements

    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

  • March 20, 2012 at 10:02 am

    #1461739

    Full backup is exactly that - Everything

    Differential backup is all of the changed pages since the last full backup

    Transaction log backup is all of the transactions since last transaction log backup and will aloow you to restore to a point in time.

    So you need to look at how often your data is changing, because if it changes alot then the differential backup could be as large as the full.

    Also when / if you need to restore you have to apply them in the order full backup followed by last differential (if any) and then all of the transaction log backups since the last differential / full if no differential

    Hope this helps a bit

  • March 20, 2012 at 10:57 am

    #1461780

    As Gail mentioned, you need to know your company's requirements. For example, our company does not require point in time backups and require a maximum data loss of 1 day. So, we have all of our databases in simple recovery mode. Small databases get a full backup daily and we keep a week's worth, larger databases get a full backup every Sunday with a differential daily, really large databases have partitioning with read only file groups that are backed up and archived while the read write file groups get a weekly backup and daily differential.

    It all depends on your company's needs, tolerance for risk, acceptance for loss of data, and acceptable recovery time. These need to be balanced with budget for the required hardware, software, and location of everything.

    Jared
    CE - Microsoft

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

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