Post reply

Verify my current backup strategy n suggest

  • April 7, 2009 at 3:41 pm

    #202658

    T-Log backup - Every 15 Minutes and Retention Period - 2 Days

    Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)

    Full Backup - Every week and Retention Period - 1 week (Till next full backup)

    This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...

    two databases are highly transactional

    Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..

    Thanks,

    Sudhie.

  • April 7, 2009 at 3:53 pm

    #973866

    Sudiendra (4/7/2009)

    Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)

    Full Backup - Every week and Retention Period - 1 week (Till next full backup)

    What happens if one of your backups is damaged and unusable?

    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

  • April 7, 2009 at 3:59 pm

    #973869

    valid point...Will increase in retention period override this Problem?

  • April 7, 2009 at 4:34 pm

    #973889

    Sudiendra (4/7/2009)

    T-Log backup - Every 15 Minutes and Retention Period - 2 Days

    Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)

    Full Backup - Every week and Retention Period - 1 week (Till next full backup)

    This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...

    two databases are highly transactional

    Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..

    Thanks,

    Sudhie.

    And also what is your clients business model WRT to data loss? and also are you offloading it to a tape?

    and include to verify your backups

    Backup Mantra:

    Backups, Early backups, Frequent backups and verified backups

  • April 7, 2009 at 4:41 pm

    #973891

    Krishna - We have SAN replication enabled. Although i know nothing much about that..all drives are replicated to another server..

    Now considering SAN replication, will this strategy work?

    BTW, Backup drive is not replicated

    Thanks,

    Sudhie.

  • April 7, 2009 at 4:50 pm

    #973894

    Sudiendra (4/7/2009)

    Krishna - We have SAN replication enabled. Although i know nothing much about that..all drives are replicated to another server..

    Now considering SAN replication, will this strategy work?

    BTW, Backup drive is not replicated

    Thanks,

    Sudhie.

    Sorry Sudiendra, I don't have much idea about SAN drives. Lets wait for someone to answer this for us.

  • April 7, 2009 at 5:29 pm

    #973904

    Sudiendra (4/7/2009)

    T-Log backup - Every 15 Minutes and Retention Period - 2 Days

    Differential Backup - Every day and Retention Period - 1 day (Until the next Diff backup)

    Full Backup - Every week and Retention Period - 1 week (Till next full backup)

    This is for all databases on a server, 2-3 databases are around 300 GB and rest of them are smaller than 100...

    two databases are highly transactional

    Are you seeing any flaws? Because i don't want to risk my client for my mistake (If any)..

    Thanks,

    Sudhie.

    You didn't mention how often you are backing up the disk backups to tape. No backup strategy is complete without taking tape backups into account.

    I prefer to do a full backup every day, if possible. I usually keep the transaction log backups at least three days. Since I have a full backup every day, I usually do not do a differential backup. I expect that all backups on disk will be backed up to tape every day.

  • April 7, 2009 at 5:31 pm

    #973907

    Michael Valentine Jones (4/7/2009)

    We have San replication, so we switched off tape backups..

    Thanks,

    Sudhie.

  • April 7, 2009 at 5:37 pm

    #973911

    Michael Valentine Jones (4/7/2009)

    Since I have a full backup every day, I usually do not do a differential backup.

    Michael,

    so If a disaster happens would you restore the Full and all the subsequent Transaction log backups? but that would be many Tlog files to backup, say if you are taking for every 15mins.

    Incorporating differential backups into the plan will increase the available recovery points between full backups, isn't it?

  • April 7, 2009 at 5:45 pm

    #973917

    Yes, you will need to restore the transaction log backups created after your last full backup up until the point of time you want to recover to.

    It is no different if you have differential backups, except that you only need to apply the transaction log backups created after the last differential backups.

    Differential backups do not increase your available recovery points, since you can recover to any point in time with transaction log backups.

  • April 7, 2009 at 5:51 pm

    #973921

    Sudiendra (4/7/2009)

    Michael Valentine Jones (4/7/2009)

    We have San replication, so we switched off tape backups..

    Thanks,

    Sudhie.

    That is an extermely bad idea. If your database becomes corrupt, you will have nothing to recover from, because the corruption will be replicated by the SAN also. You should turn your tape backups back on immediately.

    Think that can't happen? Read the links below about a company that used your backup strategy and went out of business because of it.

    Mirroring is Not a Backup Solution

    http://hardware.slashdot.org/article.pl?sid=09%2F01%2F02%2F1546214&from=rss

    "Journalspace.com have fallen, never to return. The post on their site describes how their entire database was overwritten either through some inconceivable OS or application bug, or more likely a malicious act. Regardless of how the data was lost, their undoing appears to have been that they treated the drive mirroring as a backup and have now paid the ultimate price for not having point in time backups of the data that was their business."

    Why back up? Ask JournalSpace

    http://www.brentozar.com/archive/2009/01/why-back-up-ask-journalspace/

  • April 7, 2009 at 6:07 pm

    #973929

    I agree with that article, But for some jobs we are suggested not to run backups in parallel. So we are not finding good time to accommodate this long running backups.

    In this regard, we have chosen this backup strategy. I have decided to increase retention period now from our discussion.

    I am still talking to admins on backup strategy for the drive which holds backups.

  • April 7, 2009 at 6:09 pm

    #973930

    Thanks for quick turn around every one, keep them coming. I will post an article on SAN replication if i get it.

    BTW, this is my first longest thread in ssc 😛

    Thanks,

    Sudhie.

  • April 7, 2009 at 6:14 pm

    #973931

    Any backup strategy that does not include getting your database backups to tape is incomplete and a disaster waiting to happen.

    It doesn't have to be database corruption. It could just be data that is deleted from the database or updated by accident and is not noticed until after all your backups on disk are gone.

    I have had to recover data from tapes that were two years old.

    Don't become the latest post on this thread:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

  • April 7, 2009 at 6:37 pm

    #973936

    Michael Valentine Jones (4/7/2009)

    Don't become the latest post on this thread:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

    OMGGGG!!!:w00t: That's the scariest thread I have ever seen, I am going to advise the same to rest of my DBA's to look at the thread 🙂 or even my management and show them how important it could be though I always stress more on our DR strategy always explaining to them how important they are. Now this wil give a better understanding to them not understanding but consequences. Thanks michael

Viewing 15 posts - 1 through 15 (of 25 total)

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