Jan 20 2014

Time for a Quick Rant

This is an actual quote from what we can only assume is a functional human being:

The database is very big so we stopped taking backup’s.

Eight lords a leaping are you kidding me? Seriously! Seriously? By the Great Gu and all the Valkyries in Valhalla, you stopped taking backups of your PRODUCTION database because it was “very big.” And I’ll put down Brobdingnagian stacks of cash that “very big” in this case is probably 200-500gb or at worst 1-2tb. People, assuming you have enough brain stem intact to regulate breathing, you must know, you must by all the sparkly vampires in Twighlight KNOW that you need to have backups. Right? I mean, nothing ever goes wrong on this shiny marble we call Dirt, does it? No one would EVER just run a DELETE statement in production without a WHERE clause, would they you hairy bottomed tree climbing mouth breather? And I’m sure that, if it happened, you could just blow magic unicorn powder at the server in order to get the company’s billing list back, right? Because without that backup, you’re relying on the undivided attention, and total positive intent, of Odin and Freya to ensure that you never, oh, I don’t know, lose power causing the rocker arm on the disk to bash down repeatedly on the platter like Thor’s Hammer on an Ice Giant’s head, with similar results for your database. Until you have so much data that EMC hosts your company’s holiday party, for free, there is enough disk space, somewhere, to take a backup of your database.

Now, get out there and get it done. Don’t make me travel to each and every one of your places of work with the lead-weighted hickory learning bat to lay some education up side your beanie holder. Please, just take a backup.

I really mean it this time. Backup your database

I really mean it this time. Backup your database

34 Comments

  • By Jason Strate, January 20, 2014 @ 10:18 am

    Here, here. There might also be ways to make those “really big” database backups smaller… with compression tools or options.

  • By Joey D'Antoni, January 20, 2014 @ 10:18 am

    In the 1-2 TB range this is crazy, BUT I have made the recommendation to stop taking backups before. At a former employer, we had a 55 TB database with 3 TB of transactions a day (that could be recreated from source systems). It probably shouldn’t have been in a RDBMS, but it made no sense to take backups, because there was no way they were ever going to restore. But this is a .01% exception.

  • By Jason, January 20, 2014 @ 10:21 am

    That is one of my favorite excuses for not taking a backup. Another favorite is “i’m too busy” (e.g. http://bit.ly/1brSXT5 ).

  • By Grant Fritchey, January 20, 2014 @ 10:23 am

    Hey Joey, I’d agree. 55TB is way too large to realistically backup, at least using standard mechanisms. But, as you say, it’s an edge case. People far too often use edge cases to justify an action when they’re no where near being in the same situation.

  • By SQL4GNT, January 20, 2014 @ 11:24 am

    I’d like to know 2 things:

    1. How much do you charge for lead-weighted hickory learning bat sessions. I know some people that need that experience :)

    2. Where are you ordering your magic unicorn powder? I’m all out

  • By byron, January 20, 2014 @ 1:04 pm

    not one, but two of the best things i’ve read this year:

    “you must know, you must by all the sparkly vampires in Twighlight KNOW that you need to have backups…”

    “Until you have so much data that EMC hosts your company’s holiday party, for free, there is enough disk space, somewhere, to take a backup of your database.”

  • By Grant Fritchey, January 20, 2014 @ 1:24 pm

    Thanks. But, of course, it’s a very short year so far.

  • By Kevin Kline, January 20, 2014 @ 2:44 pm

    Great post, Grant.

    One thing I always point out to people who say things like “my database is too big to backup”, is that backups are not the same thing as business continuity.

    So while they may not run SQL Server backups, they might be able to better ensure business continuity at some other level, such as simple SAN duplexing. Again, that’d be really expensive for such a large database. But serious, do you really want ZERO recoverability?

    And as Joey mentions, you might have to use non-standard approaches, such as partitioning the database, then backing up only the most recent partition.

    There are lots of ways to skin this cat, but to say “No, I just can’t do it” seems like the laziest of all answers.

  • By Grant Fritchey, January 20, 2014 @ 3:02 pm

    I’m sure this will shock the heck out of you Kevin, but that was a great set of points. Thanks for adding them to the discussion. I agree completely.

  • By Tom Roush, January 20, 2014 @ 3:05 pm

    last time I had a database that was too big to back up on the box it was on, I striped it to multiple boxes (keeping track of where things were, of course) Interestingly, it was in a DR situation. I didn’t see the original post, but as Kevin said earlier, there are many ways to skin this cat. This would be one of them.

  • By SQL4GNT, January 20, 2014 @ 5:24 pm

    I can’t believe that quote would come from a DBA, unless he’s been tenured (can that even happen?). Business continuity aside, at least discuss with the business what data is critical (we will die without), discover what data is static (config tables, translation tables?), and try to come up with some sort of backup. I’d really like to know how big “very big” is?

  • By Robert L Davis, January 20, 2014 @ 6:16 pm

    Hah! I’ve heard that same statement as well. Though it was more like, “we don’t know how to back up something that big.” Excellent comments above too along the line of VLDBs and not “needing” backups. Along those lines, I’ve written a blog post inspired by this one about performing schema-only backups and restores: http://www.sqlsoldier.com/wp/sqlserver/schemaonlybackupsandrestores

  • By Allan Hirt, January 20, 2014 @ 7:39 pm

    I’ve heard this as well over the years. While large is always relative, as has been pointed out by Joey, unless you’re in some egde case (and in his case, the data could be reconstructed – but I’m sure painfully), you need backups.

    No one thinks about the “oops” factor. What happens if your genius storage or Windows guys installed a new HBA driver that corrupted your data? It’s not just the DELETE or UPDATE without a qualifying WHERE clause (and even then …).

    The problem is people rarely think about nearline data – that is, the stuff most frequently used/updated vs. archival or read only data. Out of a 55TB database, is 1% active? 10%? 50%? You devise its backup scheme and underlying disk structures accordingly. All of a sudden you’re not dealing with Planet Earth, but maybe Manhattan.

    The best excuse I’ve heard to date is, “We have clusters, we don’t need backups.” No, really.

  • By Grant Fritchey, January 20, 2014 @ 7:41 pm

    Nice article Robert. Just added it to my reading list so I can point a few people to it. Thanks!

  • By Grant Fritchey, January 20, 2014 @ 7:46 pm

    But what if I have clusters and you, then I really don’t need backups. Ha!

    I’ve seen people say that they don’t need backups because they have RAID storage. I’ve also had people just flat out say, “How often do you need backups.” Which is frightening as hell. I’ve only really needed them a few times in serious crunch times, but, oh my, did we really need them.

  • By Marc French, January 20, 2014 @ 8:08 pm

    Dude, you are still intimidating people

  • By John Sterrett, January 20, 2014 @ 8:32 pm

    Nice rant Grant. It’s amazing how many companies out there don’t have backups let alone tested them. I wonder if there is a list of articles about companies that went out of business because they didn’t have a database backup to restore.

    I cringe every time someone tells me they haven’t tested their disaster recovery plan.

  • By Grant Fritchey, January 20, 2014 @ 8:53 pm

    Marc!

    I was talking to someone recently who mentioned you. Oh, and that you had taught me everything I know. We may need to have a chat. Ha!

  • By Grant Fritchey, January 20, 2014 @ 8:55 pm

    Hey John, I have a small list I put together. https://www.simple-talk.com/sql/backup-and-recovery/backups,-what-are-they-good-for/ I actually had a hard time tracking down good stories about this. I’ll bet there are a lot more of them, but there’s not much documentation.

  • By Morticia, January 21, 2014 @ 6:35 am

    “hairy bottomed tree climbing mouth breather”

    Classic !

  • By dan holmes, January 21, 2014 @ 9:34 am

    A followup to a followup (Robert Davis’) but they are both ultimately inspired by this post.

    http://dnhlmssql.blogspot.com/2014/01/headed-down-trail-with-my-dacpacthen-i.html

  • By Grant Fritchey, January 21, 2014 @ 12:50 pm

    Another good contribution. Thanks Dan.

  • By Kenneth Fisher, January 21, 2014 @ 6:15 pm

    Great rant! But I thought the NSA was backing up all of our databases for us and we just had to ask them for a copy when we need one?

  • By Grant Fritchey, January 21, 2014 @ 6:33 pm

    I have not yet seen a successful restore from the NSA, so I’m not sure I’m ready to rely on them as my primary means of data recovery just yet.

  • By Dave Schutz, January 22, 2014 @ 2:29 pm

    As I think Alan Hirt was alluding to, file group backups. At least backup up the important parts of the data. One way to get a skinny cat.
    And test those backups and restores.

  • By Scott, February 6, 2014 @ 6:02 am

    Yep, out of the two dozen or so clients I’ve had it’s the same issue I have had to deal with, ie – database becomes so large, and too critical to the business it becomes a “monster” that people are too scared to do anything with; including any form of backup.

    Just be glad that us DBA’s exist and can do something about it. ;o)

    S.

  • By Mark Walters, February 25, 2014 @ 7:54 pm

    My usual here is no one ever lost their job for failing to take a backup. But you will lose your job if you cannot get the business back up and running (restore).

  • By Jim McCarthy, February 27, 2014 @ 2:27 pm

    If he is so worried about the disk space and how big the DB is, I have his solution. Stop SQL services, delete the large DB, and move on..voila..disk space recovered. This also gives him plenty pf room top store his resume, because it better be up to date.

Other Links to this Post

  1. Schema-only Backups and Restores | SQLSoldier — January 20, 2014 @ 6:06 pm

  2. (SFTW) SQL Server Links 24/01/14 • John Sansom — January 24, 2014 @ 7:00 am

  3. My links of the week – January 26, 2014 | R4 — January 26, 2014 @ 9:59 am

  4. Approachable? Sometimes! | Home Of The Scary DBA — February 25, 2014 @ 10:17 am

  5. DBA’s Have No Control Over Azure SQL Database | Mike Donnelly, SQLMD — June 23, 2014 @ 11:38 pm

  6. What does it mean that a value is NULL? | SQL Studies — July 14, 2014 @ 7:56 am

RSS feed for comments on this post. TrackBack URI

Leave a comment