Safety in Numbers

  • Comments posted to this topic are about the item Safety in Numbers

  • It is quite difficult to get your head around this one, Deciding what to keep and what not to.

    I always keep the last Three Full Backups, the Transaction Log backups and Differential Backups between them.

    Once the Full Backup is performed on Sunday night, these are restored on a Test DB on monday morning, if they fail, Back it up again.

    I became complaisant with the backup routine and decided that One Full was enough after IT complained i was using too much disk space on the SAN for storing the DB backups and suggested I alter my backup routines... so I began to keep only the last full and the transaction logs after, but got seriously burned one day a few months ago when the backup on the disk were corrupted and the DB server went down on monday morning before i came in due to a hardware failure.

    We live and learn, but that day in August will probably remain with me forever more...

    Leon

  • About full backups, we take one each night for each database. We store them at a different safety location. I believe requirements might vary a lot depending on the company. I'm btw a Consultant at a Company where you can purchase a pension insurance and you have funds to chose between and thus you get a yield on the pension.

  • I used to retain the last 2 Full AND Transaction Log backups pending verification of a RESTORE into a shadow DB however, after listening to sob stories from the IT geeks that my disk usage had risen from 4 GB to >16 GB, I dropped down to retaining the last Full Backup only.

    I had an erraneous backup on my main DB recently which only came to light when I attempted a RESTORE into my shadow DB. It was a nervous couple of hours before I could RESTORE another hastily triggered Full backup, an event that highlighted how precarious and inadequate my maintenance plan was.

    I've since returned to retaining my last 2 Full AND Transaction Log backups.

    Glen Parker 🙂

  • I've typically kept two full backups (the most recent and the one before that) along with the log backups between them, just in case.

    Hi Steve, nice article. It is always necessary to get other people's take on backups. I also keep two full backups but only logs up to the last full backup. Why would you want to keep log backups between? Like to hear your ideas.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • 2 backups on disk (3 for system tables), 7 days on on site disk/tape, copies offsite.

    Restore of main database every month from Live to Dev environment to

    a) eliminate any baseline differences in code (bad change control mentality by developers to blame aka "It works so what?")

    b) test backup

  • Right, just re-read your post, you're not interested in tapes.

    Personally I don't see the difference, the only difference between keeping a backup on tape or on disk is that disk is quicker to restore.

    Maybe I'm being thick here but a file is a file is a file.

    Isn't it?

    Anyway, a week, from disk.

  • Where I work we keep every single backup, we keep an immediate copy here in the building of the previous day's data, then backup the previous backup to another server somewhere else in the country. There at the other location I think we operate the father father son theory in which you hold 3 copies of that data. Depending on which DB and how important the data is on that DB, we have more/less backups than what i've just said, but thats the typical setup we have here for standard importance DBs

  • dcooper 32364 (2/12/2010)


    Where I work we keep every single backup, we keep an immediate copy here in the building of the previous day's data, then backup the previous backup to another server somewhere else in the country. There at the other location I think we operate the father father son theory in which you hold 3 copies of that data. Depending on which DB and how important the data is on that DB, we have more/less backups than what i've just said, but thats the typical setup we have here for standard importance DBs

    In addition to this, we have some data that is so Old that it's stored on tapes and restoring it is a 2 man job, one person has to scan the data while someone else is manually inserting and removing the next tape,

  • I work in the Insurance industry. We do full backups every weekday to disk and then to tape. The tapes are then sent offsite. The tape rotation we use allows us to restore to any position within the last month and any month end position that we have a tape for. We keep all monthly tapes forever - but expect that the tapes will become unreadable way before then. Is anyone else subject to eDiscovery procedures?

  • Hi,

    We keep daily full backups for four weeks, and take log backups every 15 minutes which are stored for the current and the previous day. Everything is stored on (slightly) remote NAS as storage is no issue any more.

    You might ask, why so many full backups? The simple answer is, our experience shows that you cannot rely on the consistency of every backup taken, even if it is read verified after write. So we want to keep the backup chains as short as possible to get more potential restore points just in case we need them.

    Best regards,
    Dietmar Weickert.

  • Full backups every night and log backups every 30 minutes, all of which we keep for eternity. Our production database is pretty small anyway (1 GB), so we're lucky there. Keep offsite backups of them. We do this because we find that we always end up having to restore to a separate database different points in time, going back years sometimes, to fulfill a customer request. We don't have the time to build an archive/retrieval system yet.

  • For me, it depends on the data loss policies of the company, and the ability to recover data.

    For example, I used to manage a database that was just a tool for managing and processing mailing lists. Since the lists themselves existed outside the database, and could be re-imported and re-processed pretty easily, and since the database itself was huge (terrabytes), I didn't bother with anything beyond a weekly full backup and daily diff backups.

    For another example, I used to manage a database with business-critical financial data, where the loss of any data at all could have catastrophic consequences to the business. For that, I kept the most recent two full backups, and the log backups for those, on disk, and kept a full history of full backups off-site in a vault (that last was partially due to legal requirements). With that, and the backups being tested right after they were taken (both checksum and actual restore), I could be pretty certain I could recover to at least the most recent log backup. And since backups, logs, and data files were on separate SANS, I could be pretty certain I could deal even with a full disk cratering on one of the SANS without data loss.

    Most of what I've worked with has been in between those.

    So, it depends on the business need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I keep one full and the associated t-log backups local to each server and on our backup server. We also have a week's worth retained on that backup server as Backip Exec backups, so we can go to disk for up to a week. After that, we have to go to tape.

  • Backups? What are backups?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

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