Voice of the DBA

Tag: disaster recovery

  • Always restore with NORECOVERY

    Always, always, ALWAYS restore a SQL Server database with the NORECOVERY option.

    It’s trivial to switch the database online.

    Not trivial to recover from an accidental restore with RECOVERY (the default)

    ‘nuff said.

  • The Colocated Dangers

    Titanic Poster
    It’s not “if”, but “when” you will have a disaster.

    We went down.

    It was quite a surprise for me, and something that hasn’t happened in a long time. It wasn’t for long, but a month ago, there was a period of time when SQLServerCentral was down. It’s also one of the very few times it’s happened since I was in charge of the servers. The exact reason was something to do with out hardware, and it was fixed relatively quickly by our hosting provider.

    Early on in the site’s history we had a few hiccups. I was the one that drove out to a co-location facility to replace hardware or fix something that was broken. There were a few times where all that was required was a reboot, but we didn’t have the “remote hands” service available at a few of our hosting facilities. Fortunately for me I had an understanding boss that would allow me to flex and extend my lunch time during these emergencies.

    Since Red Gate purchased SQLServerCentral, we’ve had excellent uptime. A few outages for upgrades of the site, a minor DNS issue, and a few hiccups from maintenance by our hosting facility, but all very short. This hour-or-so long outage was the first unplanned outage in years. However it reminded me of a somethings: it’s not if a disaster will occur, but when it will occur?

    Putting all your data (and the servers it lives on) in one place is the fact of life that most of us have to live with, but we ought to have some idea of a DR plan if our data center goes down, or our hosting company has an issue. It might not be a thoroughly documented plan, but it ought to be a thoroughly thought out one.

    At least if expect your enterprise to continue to function during the disaster and your employment to continue after.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Backup and Restore

    Rainbow Six
    The team at Rainbow Six has to be prepared for a disaster anytime.

    Today’s editorial was originally published on May 13, 2007. It is being re-run as Steve is at SQL Server Connections.

    I think that it’s important to practice restoring and rebuilding your systems. You never know when you’ll encounter problems and need to restore your data onto another piece of hardware. It’s definitely something that people don’t often practice and with documentation not usually being up to par, it’s something that people should be comfortable with performing.

    I saw this article for CIOs that seemed to suggest that IT people aren’t testing their systems enough. The statistic in the article was that 89% only test once a year. Also that 67% were only minimally confident that their disaster recovery system would work as planned.

    I think it’s pretty good that 89% test their systems. I’m guessing that 11% do it more than once a year, but I’d be concerned if 89% hadn’t tested it at all! I think once a year is a pretty good plan given the hectic scheduled, heavy workloads, and the lack of buy-in by many executives for a DR plan. It’s not like we’re the guys from Rain bow Six that could be called on multiple times to perform this year. In all likelihood most of us will never encounter a disaster in our careers.

    And if you do, it won’t go as planned. I’ve had disasters and disaster tests in my career and none of them goes as planned. It’s like I’ve seen quoted: “No battle plan ever survives contact with the enemy.” We could easily paraphrase this for IT and note that no DR plan will every survive a disaster. You need people that can adapt, think in the heat of the moment, and get things working when the plan doesn’t cover every contingency.

    As DBAs I think most of us tend to practice the DR stuff on a more regular basis as part of our jobs. Many of us are constantly restoring databases for QA and development, getting practice that sysadmins don’t usually get. We also have the advantage of being separated from the hardware and we can install SQL Server and restore databases on any Windows server host.

    I think you should practice your DR skills in little bits. Maybe grab the tail of the log and do a point in time restore for your next QA cycle. But practicing a full plan more than once a year isn’t practical for most of us.

  • Helpful Advice?

    hard drive
    Flicking power on and off is not recommended for these devices.

    This editorial was originally published on Feb 5, 2007 and is being re-run as Steve is on vacation.

    I have to be honest with you. It wasn’t me. This advice on DRwasn’t given to anyone by me. This post is from a blog entry last year before Tech Ed. I made a note of it, but it wasn’t until recently I got around to actually writing about it.

    I’m kind of surprised to see some of this advice being given to people. Some of these I don’t think are too bad, but would you do any of these on your production database?

    1. “Just run REPAIR_ALLOW_DATA_LOSS and you’ll be fine…”
    2. Just rebuild your transaction log using these steps…”
    3. “Just restore your database and carry on…”
    4. “Run CHECKALLOC, then CHECKDB, then CHECKTABLE on all your tables, then…”
    5. “Just flick the power switch on and off a few times on one of the drives…”

    Actually I’ve done #4 and #3 is something I’ve had to do before as well. I can appreciate the caution in the article about not finding the root cause, but I’ve had more problems than I’d like to think about where we couldn’t find a root cause in a reasonable time and decided to move on. And we never had the issue again. In the interests of getting the business going, there was a time where I explained everything, guesstimated the data loss, and we decided to just restore, lose the data, and have people re-enter it as quickly as possible.

    As for the other items, I don’t think I’d run REPAIR_ALLOW_DATA_LOSS without someone from CSS on the phone. And I don’t think I’ve ever even heard about anyone “rebuilding” a transaction log. That sounds like one of those urban myths where someone heard that someone said that they had a way to rebuild a log.<

    Flicking the power on your drives? I’m not sure what I’d even say to someone who suggested it.