When did you last backup your home/personal/less-critical system… is it really less-critical?

Have you ever written something and then lost it… for whatever reason: your own stupidity (come on we’ve all accidentally done something at some point where we lost data or a spreadsheet or a document or something…), the software eats it (this might be self-inflicted but I’ve been in apps that just hang and that’s it… there’s nothing you can do except power off), or….whatever.

Well, during those times… have you ever thought – I’d do almost anything to get that data (and time) back?

Before I go any further – yes, backups are good. No, let me be clearer… Backups are an absolute requirement of ANY environment!!! 

Even personal/home environments should have something to protect the data. Something to consider is even offsite copies. Burn a DVD or two of your precious family photos and then swap DVDs with a friend… there’s your simple/cheap/offsite data recovery. We all focus on critical data when a business depends on it – what about the personal stuff… Paul and I always talk about database backups and this post is not really targeting enterprise systems or even business critical systems… Really, there’s NOTHING THAT SHOULD EVER REPLACE A GOOD DISASTER RECOVERY STRATEGY…

Having said that though, I have an interesting story (with a data-saved/happy ending)!

I was on a plane flying back from SQL Connections (I was actually flying from Tampa after having visited family in St. Pete) and well, disaster struck again (yes, this has NOT been a good year for hard drives for me as this was my 3rd and MOST catastrophic disaster so far…).

Anyway, Paul and I were “discussing” what I STILL think is a bug in a SQL function (ok, I’ll get to that in a later post) and I had been feverishly completing a multi-page blog post AND some index examples/metadata queries, etc. when my laptop slipped off of my crappy airplane table (I was in an exit row so I had a table that came out of an arm chair and folded – it was very wobbly) and crashed to the floor (and, the irony of being in an exit row… had I been in a regular seat there wouldn’t have been room for the laptop to have fallen to the ground :).

To my surprise, I picked it up and still saw the screen I was working on. I typed in another line… and then got a little dialog that said “windows hard error” or something… I don’t even remember now. The only thing I could do is power it off. My laptop was dead. Very dead. I [expect] that I had had a major head crash when my laptop hit the floor because it’s likely the disks were still spinning. However, I still did not know this at the time.

I rebooted and received “Error 2100 – Hard drive initialization error”… so, when we landed in Denver, I was off to one of the small stores to buy an eyeglasses kit (I needed a screwdriver :) and then I went to find a spot to do some laptop surgery. The good news is that I’ve had so many disasters over the years that almost none really freak me out anymore (this is probably the 10th drive I’ve had personally fail) and, I always carry at least one spare laptop… But after the terrible time I had March in India, I actually had 2 spare laptops on this trip (yes, airline security hates me even more now – oh, and Paul travels with 2… so, we go through security with 5 laptops… we get some interesting looks!).

Anyway, I took the take the drive out, loaded it into my secondary drive bay (if you travel a lot – having this second bay that can hold a primary/secondary drive is INVALUABLE as these secondary drive bay usually use the same setup (Serial ATA in my case) as the boot drive). So, if your boot drive doesn’t boot, you *might* be able to still read and/or save data by using the secondary drive. So, on a second machine, I gave it a try to see if it would spin. No dice. I even tried my third machine (my primary was XP, my secondary is XP and my backup/backup is Vista… I thought… well, maybe?) Ah, I thought wrong. My drive would never spin again. So, on my flight from Denver to Seattle, I was not overly pleased (this is an understatement to be honest – just ask Paul) because I was at least a couple of days from having done a backup AND I was even more frustrated about having lost the detailed indexing post I was working on – and even losing the code that was on screen just THAT day.

We finally got home (which seemed like a much-longer-than-normal trip ;) and I got online thinking/hoping – is there ANYTHING I can do… And, I stumbled on a reference to a possibly out of date BIOS version and the needed update which also happens to generate this SAME error. And, being hopeful (and opportunistically forgetting the drop/crash/thump which led me to this problem), I *attempted* the BIOS update and well, it didn’t recognize that a drive was attached. OK, that was my last hope. Hours lost. Let’s move over to my backup laptop and shift everything I do have backed up over…….. which I did and I was up and running that morning. Yes, I had lost a few things and yes I was pretty frustrated but, I wasn’t totally down. It wasn’t as bad as it could have been without any backups…… but, I was still annoyed.

Then, I thought… is there any other option(s)? It’s been at least 10 years since I sent a drive into a drive recovery place. So, I thought this needed some research. I wanted to see what it might take to (and/or IF I could) recover data. About 10 years ago, I had a single drive of a RAID 0 array fail and the disk recovery place couldn’t recover any of it (well, I think they could get 128kb out it and it was going to be 800 bucks). But, that was 10 years agao. Have things changed??! Hmmm… what could they do?

Anyway, I got in touch with Drive Solutions, Inc. and they gave me the rundown of what it would take to get data back. I wasn’t sure if it was really going to be worth it (especially for the costs) but I still wanted to go through the process (for multiple reasons – some of it was for the data but some of it was for this post – and to remind people of what’s possible (myself included)) given that we talk about the importance of backups and the UNLIKELY potential for data recovery off of damaged disks…

The long story short (ah, too late :) is that they can do amazing things these days (NO GUARANTEES THOUGH!!!) and they can completely rebuild the drives in a clean room – replacing drive heads, etc. Once complete, IF they get anything back, they’ll give you a directory structure of what they’ve recovered and different options for getting it back to you (DVDs of just critical directories (there’s a cost for each DVD after the first one or two) OR you can purchase a new comparable drive and they’ll copy it over). The whole process took about a week (and this was for expedited service – which was also an extra charge). However, and amazingly, they recovered everything (well, I’ve only done a bit of spelunking but so far, so good)…

And, here’s the coolest part, I was working on some SQL files at the exact time of the disaster and well, I went to the \Documents and Settings\username\My Documents\SQL Server Management Studio\Backup Files\ directory and found a directory of Solution1. In it were 3 files with similarly ugly names (~AutoRecover.~vsC.sql, ~AutoRecover.~vsC.sql~RF93d469.TMP, etc…) and the AutoRecover file was 2KB (the others were 0KB). Anyway, I opened up the file and viola! I actually recovered the .sql script I was working on at the time I dropped my laptop (well, I still blame this on Paul cause he made me lean over to talk to him and this ultimately pushed my laptop off the crappy airline table ;) ;) ;) :).

Needless to say, I am pretty amazed at what they can do now… but, I’m certainly not going to rely on that AND, it wasn’t cheap!!!

So… what did I learn??!

1) First and foremost, data recovery is NEVER GUARANTEED. (yes, ok, we all knew that. However, I think we just need to say it out loud a few times :).
2) Even if they can recover some of the data, data recovery is not lightning fast. Even with the more expensive expedited service it takes time to ship (overnight), get the drive into a clean room, rebuild it from parts of an exactly matching (including BIOS/firmware) drive, test it, copy it to another drive, ship it back (overnight). Maybe you can find a place that’s local, that would help but, it’s still time…
3) It’s expensive… expect about $10-15 per GB. And, I guess that some will think that’s a crazy amount… Again, I had multiple motivating factors – one of which was also related to some pictures I had taken over the weekend with family in FL, some was for data, some was even better to understand this overall process… I expect that got about 10 hours back plus some photos and, I’m pretty impressed with the overall process (in general).

More than anything, I’m going to get even better at daily full system backups when I’m on the road (scheduled/overnight to an external drive) and I might even copy critical stuff to something like Windows Live Skydrive (or something like that). And, while on a plane, I might keep a small/simple USB stick handy if I do something that I really don’t want to lose while on a long plane ride. I think new technologies like “mesh” and “cloud” are really interesting and definitely the direction to better performance AND *possibly* minimizing data loss but you’re always at risk if there’s only one copy. All of this might seem crazy but well…… I’ve been called worse ;-)).

So, just to wrap things up, I’ll be doing a bit of final tweaking on my indexing blog post + my indexing demo scripts from Connections + my metadata script that I was working on at the time of the laptop disaster (which is also why it’s been a while since my last post) and I’ll be leveraging some of my favorite tools (Beyond Compare) to determine all of the differences between my recovered data/directories and the system I rebuilt from a backup (especially now that it has also changed over the course of this week since I moved over to my backup laptop). And, I’ve now ordered a new harddrive for my primary laptop. Sadly, I’m getting good at laptop rebuilding.

Thanks for reading,

PS – When did you last backup your home/personal/less-critical system… is it really less-critical?

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m


Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.