Voice of the DBA

Tag: disaster recovery

  • Risky Backups

    This editorial was originally published on Jan 13, 2009. It is being re-run as Steve is away at the SQL Intersection conference.

    The other day I was working in the forums at SQLServerCentral and saw a question about someone doing backups and having issues. While myself and others were trying to help, I recommended that this person not run multiple backups to one file. It’s something I’ve done for years and I’ve recommended, but the person followed up with a question about why I think it’s important.

    My first answer was that it’s an unnecessary risk. If something happens to the file and you’ve stored multiple backups in this file, then you lose all the backups. I’ve seen this happen with older versions of SQL Server and after that time I made it a point to never stick multiple backups in one file. To me it’s creating a single point of failure for no reason.

    But as I wrote this, I started to question myself a little. I know that some people like having the same filename every day as it makes restores simpler, especially if they’ve scripted things.  I know that file systems and hardware is often redundant and files are generally safer in the short term than they were in the past, so is my advice outdated?

    I still don’t think so because in my mind there’s a great risk of issues here and that outweighs the convenience. Not to mention that some simple  scripting can make restoring to something like QA easy enough for anyone to run.  However I’m curious what the rest of you think about storing multiple backups in one file.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • The Need for Tape

    This editorial was originally published on Mar 26, 2009. It is being re-run as Steve is away at DevConnections.

    One of our SQLServerCentral authors sent me this post, which I found to be pretty amazing. Apparently hackers broke into a backup server at WebHostingTalk, which is a community for hosting providers to discuss issues. They deleted backups first, and then moved on to deleting tables from the database.  It resulted in some downtime, and then a restore of an old copy of their database prior to trying to restore more recent copies. More information from the admin is here.

    Now that’s just malicious and nasty.

    Hopefully this wasn’t an inside job, though I could see that as a possibility. For a service like this, that just provides a place for a community, let’s people interact and talk, this is just vandalism. It serves no purpose. It’s likely no one even knows it was “M@M@sB0Y” or some other hacker, so there’s no fame, and it disrupts people who just want to converse with colleagues.

    I’d hate to think about this happening here. We have lots, and lots of posts from people all over the world, and while it wouldn’t kill us, it would really annoy many people that have volunteered their valuable time to help others by losing their work. We definitely need to make sure we don’t have an issue here.

    I see two takeaways from this incident for most DBAs and administrators. First be sure that your backup servers are just as secure as your production ones. There is production data in the forum of backup files here, and you should be providing as much security for them, and perhaps more, as on other servers. Don’t treat these servers lightly.

    Second I think this shows that there’s a need for tape backups, or some type of non-linked backup. A tape grabs the files from your server, completely separately from the SQL Server (or other application). It is a pull link, and it’s not obvious from the source server where these files have gone. That’s good in that it prevents some type of attack on the main server from propagating on. Most people use a push from their server to a remote device as part of the backup process. That’s OK, but it provides an easy link for someone to attack the backup server from the main one.

    And one more benefit of tape? Usually they’re rotated out, so even if a hacker or insider knows how to get to the backup system, without physical access they can’t touch your tape.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Versions of Disaster

    Today’s editorial was originally released on Jan 13, 2009. It is being re-run as Steve is on vacation.

    I wrote about versioning of old software recently and how I had to restore an old version of SQL Server in response to a lawsuit. We had some challenges because the backup file that we had was from years before and we weren’t sure which version of SQL Server we needed. I forget how we finally determined which service pack was needed, perhaps we read master somehow to get a build.

    In any case, when you apply patches or change how SQL Server functions, you can change the way that code is executed or even the results that might be returned to an application. You would hope that code would break and error out rather than return different results than you expect.

    Since many of us patch servers when Service Packs come out, or when we find a hot fix we need, and we are constantly deploying and changing code, do we pay enough attention to the server version as we make these deployments? I started thinking about this after the last editorial and I think that we often take it for granted that we can easily recreate our environments.

    Consider what would happen in the event of a disaster. Suppose that one of your server instances, any particular instance, died and you had to go back to a backup of the database, would you know what version of SQL Server is needed? Do you know what version each of your instances is using right now?

    In some ways this makes me think that only installing RTM and Service Pack versions in your production environment is a good idea. It’s easier to track things if you keep all your instances within a very narrow band of versions, and the worst case would be attempting a restore on RTM, then SP1, then SP2, etc. until you hit the correct version. Imagine now if you had to work through the various builds on my build list.

    I used to think that I’d want to keep current on my patches. In one large environment, we were actually pretty good about deploying patches to hundreds of instances inside a month, so we always had a large percentage of our servers, and usually all the critical servers, at the same patch level. However if a disaster had occurred within the month, we wouldn’t necessarily have been sure of what versions were installed.

    I really don’t have a great recommendation on how to handle this other than build some automated system that tracks the current build number on a daily basis, perhaps even putting it in each database. At least then you’ll have it handy in the event of a disaster.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.mevio.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • Disaster Recovery Docs–Offsite

    Years ago I worked at an ISO certified organization, where we took our administration and planning seriously. We ensured everything was backed up, we had backup drills, we each practiced recovering systems, loading tapes, even calling the company that rotated our tapes from an offsite location.

    One of our administrators was very conscientious and documented everything. He had screen images, copies of vendor documentation, all linked and labeled in a great folder on our network. We used this documentation constantly while restore systems from mistakes or drills and updated it regularly. It contained vendor contact information and account numbers. Each of us could access is remotely, and I had used it a few times from home while working on a problem with a system.

    One day our auditor came in and in the course of examining our details remarked on the comprehensive documentation and said it was great. Then he reached over and turned off our very detail-oriented administrator’s monitor.

    “Now show me your documentation,” he said.

    Our administrator was a little shocked. All the documentation was on the network. We’d accessed it from home. We accessed it from other corporate locations. We had restored it from tapes.

    We’d never thought it would be unavailable.

    It was a sobering lesson, one that had interns printing off copies that afternoon, dating them and binding them into books, one of which was always rotated offsite to the tape vendor’s facility.

    As for the tape vendor information itself? After overcoming his embarrassment, our administrator made wallet sized cards with the vendor company, phone number, address, and account number. He laminated those and gave us each one to keep on our person at all times.

    Make sure you have copies of the stuff you need offsite. It’s easy not with the “cloud”, though you better secure the data with encryption and limit the key access to a few people. You never know what you’ll need to recover, but you never should  find yourself in a position of not being able to recover your documentation.